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

 

[sandy666]

21 is a number but you use it as text "21"
try again

 

[ultracyclist]

I changed the cell format to a number and tried the same formulas, but the results are the same. Using this formula it returns a value of 1. Based on my sample data I should see a value of 4

 

[sandy666]

in formula change from "21" or '21' to 21 only

=COUNTA(UNIQUE(B16:B972,$J:$SJ="21","*Hardware*",K:K))
=SumIF (Unique(B2:B900),($J:$J='21'),($K:$K='Hardware')

 

[ultracyclist]

I tried it again with your suggested changes, but it's still not working.

 

[ultracyclist]

Can you try entering the formula in the sample data sheet I created to see if it works and repost?

Thanks,

 

[sandy666]

post your actual formulas

but it's still not working.

what does that mean?

 

[sandy666]

and no , I can't test your formula
but imho it should be
=COUNTA(UNIQUE(B16:B972,$J:$SJ=21,"*Hardware*",K:K))
=SumIF (Unique(B2:B900),($J:$J=21),($K:$K='Hardware')

 

[sandy666]

 

[ultracyclist]

I created a new Excel document and copied the data to see if something was corrupt when I copied the formulas.

When I used this formula- =COUNTA(UNIQUE(B2:B972,$J:$SJ=21,"*Hardware*",K:K)) in Cell C19 it still returns a value of 1. It should indicate 4 to match the unique values in Column B.

Book1
	A	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
18
19			1
Sheet1

Cell Formulas
Range	Formula
C19	C19	=COUNTA(UNIQUE(B2:B972,$J:$SJ=21,"*Hardware*",K:K))

Hope this helps.