Help with COUNTIFS-Multiple Criteria

[atoice]

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.

ShipAdr2 ShipCity BrandFamily Volume
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.

Thank you in advance.

 

[atoice]

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

 

[Gerald Higgins]

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 ?

 

[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:

Book1
	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

Cell Formulas
Range	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.

 

[Gerald Higgins]

Sorry, WhiteHALL not WhiteLAW

 

[atoice]

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
ShipAdr2	ShipCity	BrandFamily	Volume	Total	Number who bought both
1 PENN PLAZA	NEW YORK	MODELO ESPECIAL
WHITE CLAW
1 PENN W 34TH	NEW YORK	MODELO ESPECIAL
WHITE CLAW
1 REMSEN AVE	BROOKLYN	MODELO ESPECIAL
WHITE CLAW
1 WHITEHALL	NEW YORK	MODELO ESPECIAL
WHITE CLAW

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]88[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]123[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]143[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]59[/TD]
[TD="align: right"][/TD]
[TD="align: right"]202[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=COUNTIFS(C2:C19,"MODELO ESPECIAL",D2:D19,">0",C3:C20,"WHITE CLAW",D3:D20,">0")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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!

 

[Eric W]

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

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

Cell Formulas
Range	Formula
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.

 

[Eric W]

Shorter version of the H4 formula:

=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))