Results 1 to 8 of 8

Thread: Help with COUNTIFS-Multiple Criteria

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post 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.

    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.

  2. #2
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with COUNTIFS-Multiple Criteria

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

  3. #3
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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 ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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
    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



    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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with COUNTIFS-Multiple Criteria

    Sorry, WhiteHALL not WhiteLAW
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with COUNTIFS-Multiple Criteria

    Quote Originally Posted by Eric W View Post
    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. #7
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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
    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



    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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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))
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •