Counting Unique Cells based on 2 values in other columns

[ultracyclist]

Hi,

I need help with a formula. I’m counting unique cells in Column B and want to add criteria that looks column J for the value of 21 and Column K for Hardware. If both criteria match the unique value, it will count.

Here’s two formulas that I’ve tried with no luck

=COUNTA(UNIQUE(B16:B972,$J:$SJ="21","*Hardware*",K:K))

=SumIF (Unique(B2:B900),($J:$J='21'),($K:$K='Hardware')

Network Budget-SDWAN-Meraki-11-1-Master Final.xlsx
	B	C	D	E	F	G	H	I	J	K
1	Site Code								Purchase Year	Cost Type
2	COLB								21	Hardware
3	COLB								21	Software
4	COLB
5	COLB
6	COLB
7	COLB
8	COLI								21	Hardware
9	COLI								21	Software
10	COLI
11	PITA								21	Hardware
12	PITA								21	Software
13	PITA
14	PITA
15	LOED								21	Hardware
16	LOED								21	Software
17	LOED
US Switch Replacements(Master)

Cells with Data Validation
Cell	Allow	Criteria
J1	List	=$Q$2:$Q$3
J2:J17	List	=$O$2:$O$7

 

[ultracyclist]

Let me look in the morning and I will reply to this thread.

thanks

Tom

 

[sandy666]

If we took your example as representative then since every "Hardware" has 21 and a unique Site code, all you would need would be this.
=COUNTIF(Table3[Cost Type],"Hardware").

the same you can do with example from post#1
=COUNTIF(K2:K17,"Hardware")

imo, the best option is a PivotTable, no formula, no PQ, no vba and doesn't matter any blank columns

 

[Peter_SSs]

the same you can do with example from post#1
=COUNTIF(K2:K17,"Hardware")

Exactly, which is why I suspect the sample is not representative, why I have suggested a formula that will ensure a count of unique items with conditions, and why I pointed out that

there is nothing in that SUMPRODUCT formula that assures a count of unique values

 

[sandy666]

sure , but looks nice and result is ok