Help with COUNTIFS-Multiple Criteria

atoice

New Member
Joined
Aug 14, 2019
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Or would I use a sumproduct in front of the countifs?
 
Upvote 0
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
ABCDEFGH
1ShipAdr2ShipCityBrandFamilyVolumeTotalNumber who bought both
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
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
1ShipAdr2ShipCityBrandFamilyVolumeTotalNumber who bought both
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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! :)
 
Upvote 0
The original formula should still work. When you entered it, did you notice that that last 2 ranges were offset by 1?


Book1
ABCDEFGH
1ShipAdr2ShipCityBrandFamilyVolumeNumber who bought both
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
Cell Formulas
RangeFormula
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.
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top