Countifs with blank cells

[gberg]

I have tried to write a countifs formula to give me the number of unique combinations from the data in Column A and Column B. It works fine unless Column B is blank.

Here is the formula

=COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2)

When Column B is blank the result is always 0. How can I have it "count" the similar blank columns to give a result?

Opportunity Name	Construction Job	Occurances
901 NY Ave Conference Center	901 NY Ave (Conference Center)	2
901 NY Ave Conference Center	901 NY Ave (Conference Center)	2
AARP		0	2
AARP		0	2
AARP	AARP	1
Miles & Stockbridge	Miles & Stockbridge	1
Miles & Stockbridge		0	1
Mintz	Mintz	3
Mintz	Mintz	3
Mintz	Mintz	3

 

[Eric W]

A couple of options:

Book4 (version 1).xlsb
	A	B	C	D	E
1	Opportunity Name	Construction Job	Occurances
2	901 NY Ave Conference Center	901 NY Ave (Conference Center)	2	2	2
3	901 NY Ave Conference Center	901 NY Ave (Conference Center)	2	2	2
4	AARP		0	2	2
5	AARP		0	2	2
6	AARP	AARP	1	1	1
7	Miles & Stockbridge	Miles & Stockbridge	1	1	1
8	Miles & Stockbridge		0	1	1
9	Mintz	Mintz	3	3	3
10	Mintz	Mintz	3	3	3
11	Mintz	Mintz	3	3	3
Sheet8

Cell Formulas
Range	Formula
C2:C11	C2	=COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2)
D2:D11	D2	=ROWS(FILTER($A$2:$A$11,($A$2:$A$11=A2)*($B$2:$B$11=B2)))
E2:E11	E2	=SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=B2))

 

[kweaver]

How about:

=SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=B2))

 

[gberg]

The sumproduct solution looks the most simplistic. I will give that a go and see if I have any issues. Always such great help on this site. Thanks for the advice and help!!!!!

 

[Eric W]

Glad we could help! Let us know if you have any followup questions.

 

[Phuoc]

Another way:

=COUNTIFS($A$2:$A$11,A2&"",$B$2:$B$11,B2&"")

 

[gberg]

This site has a bunch of really smart people! Eric and Phuoc, your solutions work great, how do I mark two replies as a "solution"?

Thanks again for the help!