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]

try Power Query (Get&Transform)

Site Code	Purchase Year	Cost Type		Count
COLB	21	Hardware		4
COLB	21	Software
COLB
COLB
COLB
COLB
COLI	21	Hardware
COLI	21	Software
COLI
PITA	21	Hardware
PITA	21	Software
PITA
PITA
LOED	21	Hardware
LOED	21	Software
LOED

// Count
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    NoNull = Table.SelectRows(Source, each [Purchase Year] = 21),
    Hardware = Table.SelectRows(NoNull, each ([Cost Type] = "Hardware")),
    Count = Table.RowCount(Hardware)
in
    Count

 

[ultracyclist]

Thanks I will give it a try.

 

[Peter_SSs]

Does this do what you want?

20 11 02.xlsm
	B								J	K	L	M
1	Site Code								Purchase Year	Cost Type		Count
2	COLB								21	Hardware		4
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
Count Unique

Cell Formulas
Range	Formula
M2	M2	=ROWS(UNIQUE(FILTER(B2:B17,(J2:J17=21)*(K2:K17="Hardware"))))

 

[sandy666]

or with you example

Book1
	B	C	D	E	F
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				4
Sheet4

Cell Formulas
Range	Formula
F17	F17	=SUMPRODUCT((Table3[Purchase Year]=21)*(Table3[Cost Type]="Hardware"))

 

[Peter_SSs]

or with you example

Not sure which example you are referring to as I don't see any tables with that data in columns B:D

In any case there is nothing in that SUMPRODUCT formula that assures a count of unique values as appears to be requested going by the thread title and the attempted formulas. It just happens that in the sample given, the Site Code values were unique for 21 & hardware but try changing your cell D3 to "Hardware".

 

[sandy666]

or just use PivotTable

 

[sandy666]

but try changing your cell D3 to "Hardware".

that is why I said "with your example" or example is not representative

blank columns are not necessary to count

 

[ultracyclist]

Peter,

I tried copying your formula to my sheet, but it pops up a dialog box that says "That function is not valid" Its perplexing me that I cannot figure a way to capture the information as shown in your example. I appreciate the help. Worst case I create some filters in my sheet instead of trying to use a formula.

 

[Peter_SSs]

it pops up a dialog box that says "That function is not valid"

Since you were using the UNIQUE function in post 1, I assumed that your Excel 365 had that function and the therefore also the FILTER function but perhaps you do not have them? Can you check each one like this?

If you click the fx button and choose 'Lookup & Reference' is FILTER in the list?

 

[Peter_SSs]

that is why I said "with your example"

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").