# Thread: Help with COUNTIFS-Multiple Criteria Thanks: 0 Likes:  1 Post #5330234 (1)

1. ## Help with COUNTIFS-Multiple Criteria

Hello Community,

I cannot post pictures, so I will try to replicate a sample the best I can.

I have Column A as a Address, Column C as a Brand (Modelo or White Claw) and Column D as a quantity.

I want to count how many stores have bought Modelo and White Claw. The formula doesn't understand to break Column F separately by each store.

1 PENN PLAZA NEW YORK MODELO ESPECIAL 88
WHITE CLAW 0
Total 88

1 PENN W 34TH NEW YORK MODELO ESPECIAL 123
WHITE CLAW 0
Total 123

1 REMSEN AVE BROOKLYN MODELO ESPECIAL 0
WHITE CLAW 0
Total 0

1 WHITEHALL NEW YORK MODELO ESPECIAL 143
WHITE CLAW 59
Total 202

=COUNTIFS(C:C,"White Claw",D:D,">0") is an example for I want to know how many bought White Claw
Similar for how many bought Modelo Especial

I want to find out how I can find out who bought both. In this example, it would be 1 that bought both.

2. ## Re: Help with COUNTIFS-Multiple Criteria

Or would I use a sumproduct in front of the countifs?

3. ## Re: Help with COUNTIFS-Multiple Criteria

Hi, welcome to the board.

Is your data really all on one line for each address ?

In this example, it would be 1 that bought both
I take it you mean
In this example, it would be 1 Whitelaw that bought both
Correct ?

4. ## Re: Help with COUNTIFS-Multiple Criteria

This kind of question depends very highly on how your data is laid out, and regrettably, I couldn't quite figure it out from your sample. The board software removes multiple spaces, so the spacing gets whacked. You can use a tool like the HTML Maker in my signature, or "Go Advanced" and use the table tool. Nevertheless, I tried to come up with something:

ABCDEFGH
21 PENN PLAZANEW YORKMODELO ESPECIAL881
3WHITE CLAW00
41 PENN W 34THNEW YORKMODELO ESPECIAL123
5WHITE CLAW0123
61 REMSEN AVEBROOKLYNMODELO ESPECIAL0
7WHITE CLAW00
81 WHITEHALLNEW YORKMODELO ESPECIAL143
9WHITE CLAW59202

Sheet2

Worksheet Formulas
CellFormula
H2=COUNTIFS(C2:C19,"MODELO ESPECIAL",D2:D19,">0",C3:C20,"WHITE CLAW",D3:D20,">0")

This sort of looks like your table. If you always have MODELO ESPECIAL on the top line, and WHITE CLAW on the next, then the H2 formula should work. But I suspect it's not that simple. Let us know how close my table is to your actual table, including order of the items, and we'll take another look.

5. ## Re: Help with COUNTIFS-Multiple Criteria

Sorry, WhiteHALL not WhiteLAW

6. ## Re: Help with COUNTIFS-Multiple Criteria

Originally Posted by Eric W
This kind of question depends very highly on how your data is laid out, and regrettably, I couldn't quite figure it out from your sample. The board software removes multiple spaces, so the spacing gets whacked. You can use a tool like the HTML Maker in my signature, or "Go Advanced" and use the table tool. Nevertheless, I tried to come up with something:

A B C D E F G H
1 ShipAdr2 ShipCity BrandFamily Volume Total Number who bought both
2 1 PENN PLAZA NEW YORK MODELO ESPECIAL 88 1
3 WHITE CLAW 0 0
4 1 PENN W 34TH NEW YORK MODELO ESPECIAL 123
5 WHITE CLAW 0 123
6 1 REMSEN AVE BROOKLYN MODELO ESPECIAL 0
7 WHITE CLAW 0 0
8 1 WHITEHALL NEW YORK MODELO ESPECIAL 143
9 WHITE CLAW 59 202
Sheet2

Worksheet Formulas
Cell Formula
H2 =COUNTIFS(C2:C19,"MODELO ESPECIAL",D2:D19,">0",C3:C20,"WHITE CLAW",D3:D20,">0")

This sort of looks like your table. If you always have MODELO ESPECIAL on the top line, and WHITE CLAW on the next, then the H2 formula should work. But I suspect it's not that simple. Let us know how close my table is to your actual table, including order of the items, and we'll take another look.
Close, the total of the Modelo and White Claw is under the white claw number. which would have been row 4. Column A would have 3 rows merged (total row would be added) and Column B the same.

Hope that helps clarify Eric. I did use your formula and it came back with 0, but that could have been because the total was in a column vs. row.

Gerald: that's funny!

7. ## Re: Help with COUNTIFS-Multiple Criteria

The original formula should still work. When you entered it, did you notice that that last 2 ranges were offset by 1?

ABCDEFGH
21 PENN PLAZANEW YORKMODELO ESPECIAL881
3WHITE CLAW0
4Total881
51 PENN W 34THNEW YORKMODELO ESPECIAL123
6WHITE CLAW0
7Total123
81 REMSEN AVEBROOKLYNMODELO ESPECIAL0
9WHITE CLAW0
10Total0
111 WHITEHALLNEW YORKMODELO ESPECIAL143
12WHITE CLAW59
13Total202

Sheet2

Worksheet Formulas
CellFormula
H2=COUNTIFS(C2:C22,"MODELO ESPECIAL",D2:D22,">0",C3:C23,"WHITE CLAW",D3:D23,">0")
H4=SUMPRODUCT(COUNTIFS(OFFSET(C2,ROW(C2:C20)-ROW(C2),0,2),"MODELO ESPECIAL",OFFSET(D2,ROW(D2:D20)-ROW(D2),0,2),">0"),COUNTIFS(OFFSET(C2,ROW(C2:C20)-ROW(C2),0,2),"WHITE CLAW",OFFSET(D2,ROW(D2:D20)-ROW(D2),0,2),">0"))

The original formula is still in H2. I came up with another (longer) formula that doesn't require that MODELO ESPECIAL comes first. They can be in either order, and you can even have other brands in the column, BUT the MODELO ESPECIAL and WHITE CLAW must be in adjacent rows. If there could be other brands interspersed, then the formula would be tougher.

Also note that merged cells can be troublesome for formulas, although it doesn't seem to affect this one.

8. ## Re: Help with COUNTIFS-Multiple Criteria

Shorter version of the H4 formula:
Code:
`=SUMPRODUCT(--(MMULT(COUNTIFS(OFFSET(C2,ROW(C2:C20)-ROW(C2),0,2),{"MODELO ESPECIAL","WHITE CLAW"},OFFSET(D2,ROW(D2:D20)-ROW(D2),0,2),">0"),{1;1})=2))`