1

I'm a beginner at Excel and am currently practicing pivot tables.

The data that I'm dealing with is this table:

Table7

In the ROW Labels, I have added the field "Quiz and Assignment Mark Range" which is in a separate sheet.

Table1

In the values area, I have added the fields "Quiz Mark Range" and "Assignment Mark Range" from Table7 (both fields are summarized by count).

PivotTable Fields

My desired outcome:

Row Labels Count of Quiz Mark Range Count of Assignment Mark Range
10 - 15 67 58
5 - 10 102 100
Below 5 47 58
Grand Total 216 216

But all I get is this:

PivotTable

How do I get this fixed??

Improve this question

1 Answer 1

Reset to default
0

The pivot table is showing this result because the data is being pivoted on the Quiz Mark Range field, not the Assignment Mark Range.

If all you need to do is count the total in each category, a COUNTIF will be a much simpler solution than a pivot table.

If the summary table is laid out like this:

enter image description here

Then in cell B2 you would just need to enter the formula =COUNTIF(Table7[Quiz Mark Range],$A2), and copy that formula to cells B3 & B4. The Grand Total would then just be =SUM(B2:B4). This would give you the number of students in each Quiz Mark Range

For column C, counting the students in each Assignment Mark Range, you can copy the formulas from column B with one change:

=COUNTIF(Table7[*Assignment* Mark Range],$A2)

(Because your source data is laid out in a table (the fancy term for that kind of table in Excel is an XML table), you can directly reference Table7 and it's fields in a formula)

Improve this answer
2
  • Hey, thanks for taking the time to answer my question. I want to say that the mark range is common for both quiz and assignment marks. I haven't created separate ranges for them. So, when I add that common range table to the ROW labels, it should show the count accordingly, right? Can't this be presented as a pivot table (PowerPivot) rather than using the countif function?
    – Swetha Rao
    Commented Mar 11 at 5:55
  • @SwethaRao thanks - if the post is helpful, please feel free to give it an upvote. How have you set up the data source for your pivot table? (you appear to be referencing 2 separate sources). The way the data is currently set up, 10-15, 5-10, etc. are not fields upon which you can pivot, they are values within the fields Quiz Mark Range and Assignment Mark Range. You could put Quiz Mark Range & Assignment Mark Range in as Row Labels if you wanted to, and then also put them in as values. But the result will be a bigger table showing each of the permutations of Quiz vs. Assignment score.
    – Wizard
    Commented Mar 16 at 20:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .