Count of Unique Values Based on Multiple Criteria

[bartonlr]

Hello all!
I am responsible for pulling all of our educational attendance information each month. I pull into excel all the registration information from programs that occurred in the previous month, and then I have a set of formulas that I use a COUNTIFS and SUMIFS to see how many people attended each program and the revenue associated.

One thing I'd like to figure out is how to calculate how many groups/families come for a program. IE The Smith family brings their 4 children to programs in April, I would count them as 1 group/family. I can use the orderID (A:A) as a unique value. I can put the value in the yellow cell in my attached example.

I don’t need to know the unique values, just how many of them occur- during a particular month, for a particular category.

Lucy

[TMS]

Is this what you want:

=COUNTA(UNIQUE(TOCOL(DATA!A2:A1000,1)))

[bartonlr]

Thank TMS. Its almost what I need. I need to also filter it by date (so only unique order numbers in April) and by category (Zoo Troop vs Camp vs my many other categories not listed in my example file)
So in my summary tab, I'll be able to see something like 40 families came to Zoo Troop in April (with 50 individual children B:B), 100 families came to Camp in May (with 150 individual children B:B). And then I also divide out the families that received financial assistance (J:J promocode <>LCEAF or)

The multiple criteria is the part that's difficult.

[TMS]

Ok, this is how to limit the date by month/year:

=COUNTA(UNIQUE(TOCOL(FILTER(DATA!A2:A1000,(YEAR(DATA!C2:C1000)=YEAR(D1))*(MONTH(DATA!C2:C1000)=MONTH(D1)))+1)))

=COUNTA(UNIQUE(TOCOL(FILTER(DATA!A2:A1000,(YEAR(DATA!C2:C1000)=YEAR(G1))*(MONTH(DATA!C2:C1000)=MONTH(G1)))+1)))

I'll leave you to add the other filters.