I\'ve designed an Excel 2007 table. The columns are date, name, task, no of days

Question

I've designed an Excel 2007 table.

The columns are date, name, task, no of days, task, task category, cost per day, total cost.

I am trying to count the unique individual names in column b. I am using the formula:

=SUMPRODUCT(([Name of Volunteer]<>"")/COUNTIF([Name of Volunteer],[Name of Volunteer]&""))

which I found on this site (given by T Valko).

The formula works well. However, when I filter the table to see the individual number of names in any given quarter, the number doesn't change. It is still counting the whole column. How can I get the formula to filter the result so that it changes, according to the filter I apply?

Finally, I am also trying to create a pivot table to show:

task category, no of days and total number of unique individuals by year and quarter. I think it is the unique number of individuals that is the hard part!! I'm stumped and any help on these two issues would be much appreciated!!!!

Explanation / Answer

You can use this to count the unique values in a filtered list. Note that I haven't used table referencing because to formula gets long. In this b2:B7 is the column excluding the header we're counting.

Note these are bot ARRAY formula. See below for how to enter them

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B7)-ROW(B2),,1)), IF(B2:B7<>"",MATCH("~"&B2:B7,B2:B7&"",0))),ROW(B2:B7)-ROW(B2)+1),1))

If you want to use table referencing this is it. Note that B2 refers to the topmost cell (Not the header) in the column we're trying to count.

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2,ROW(Table1[Name of Volunteer])-ROW(B2),,1)), IF(Table1[Name of Volunteer]<>"",MATCH("~"&Table1[Name of Volunteer],Table1[Name of Volunteer]&"",0))),ROW(Table1[Name of Volunteer])-ROW(B2)+1),1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.