Count unique values with a criteria (Excel 2016)

[lucy1990]

Hello,

i could use a kind help for a problem i can't solve by myself.

I'm trying to count unique values for some statistics and i found a little help from the web for the general unique count off a table, which would be for the attached workbook for the basic count of every person name involved in the data like so:

=SUM(IF(FREQUENCY(MATCH(data[Person],data[Person],0),MATCH(data[Person],data[Person],0))>0,1))
(black table is called "data", blue table is called "count")

Besides me not seeing through that formula that works perfectly for what i need, i just crafted together in a "ah this seems to be that one" type of way and it totally gives me the right count as asked.

BUT now i need a detail added:
What if i want to consider a count with a criteria from a second column. Which in the example case would be the column "project" and count uniquely what persons work on a specific project and give the results as the red numbers say in the blue "count" table. Would it be possible to add this criteria in the above formula? Or if not, every advice and explanation is welcome!

I don't have Excel 365, so i don't know about the UNIQUE formula and can't use it in my Excel 2016.

P.S. i wanted to avoid pivot tables and lists like "advanced filter" or so, please! I just need numbers off my data, no listings

Thanks,
Lucy

[menem]

If you don't mind to use helper column.

Insert 2 columns between D and E
in E2 use formula
=IF(COUNTIFS(B$1:B2,B2,C$1:C2,C2)=1,1,0)

Then just use countifs in answer tabel (H2)
=SUMIFS($E$1:$E$9,data[[#All],[Project]],[@Projects])

Regards.

[lucy1990]

Hello menem,

thx for your reply!

Adding columns would be a bit long-winded as i have about 100x tables with a lot of data IRL there is no other possible way to extract the values?

[CARACALLA]

F2=IFERROR(SUM(IF(FREQUENCY(IF($C$2:$C$100=$E2,MATCH($B$2:$B$100,$B$2:$B$100,0)),ROW($B$2:$B$100)-ROW($B$2)+1),1)),"")

control+shift+enter

copy down

[lucy1990]

hello Carcalla,

the first 2 projects show me blank cell and the last one it counts 1, when i do as you say

[CARACALLA]

Sorry

H2=IFERROR(SUM(IF(FREQUENCY(IF($C$2:$C$100=$G2,MATCH($B$2:$B$100,$B$2:$B$100,0)),ROW($B$2:$B$100)-ROW($B$2)+1),1)),"")

Control +shift+enter

copy down

[XLent]

you can also use a SUMPRODUCT approach - this is no more efficient (less so, in fact) but does negate the Array entry requirement

=SUMPRODUCT(($C$2:$C$9=$E2)/COUNTIFS($B$2:$B$9,$B$2:$B$9&"",$C$2:$C$9,$C$2:$C$9&""))
committed with Enter
copied down