tricky multi-condition counting
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: tricky multi-condition counting

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm having troubles with a seductively simple-sounding task.

    Picture this -- a 3x3 table beginning in cell A1 (without column headings), as follows:

    DELIVER FRUIT BAGS
    PICKUP VEGETABLES BOXES
    DELIVER FRUIT BOXES

    The task is to count how many order-lines (i.e. how many rows) require either fruit or boxes, but only for delivery orders.

    I've been tackling this in two parts, with the first being to only pay attention to "DELIVER" rows, and the second part being to perform the count of rows with either "FRUIT" or "BOXES".

    Starting with the second part --
    My first hiccup was realising that COUNTIF was unsuitable for handling multiple conditions. So I went for the following array formula:
    {=SUM(($B$1:$B$3="FRUIT")+($C$1:$C$3="BOXES"))}
    However this gives a result of 4, when (at this stage) I need a result of 3. The missing piece of this section of the puzzle was that I needed to subtract a duplicate count when a row contained both "FRUIT" and "BOXES". The following array formula gave the correct result:
    {=SUM(($B$1:$B$3="FRUIT")+($C$1:$C$3="BOXES"))-SUM(($B$1:$B$3="FRUIT")*($C$1:$C$3="BOXES"))}

    Now, for the first part, to limit the counts to only those rows with "DELIVER" --
    Here's where things are going haywire. I thought I could simply enclose the above formula inside an IF statement as follows:
    {=IF($A$1:$A$3="DELIVER",(SUM(($B$1:$B$3="FRUIT")+($C$1:$C$3="BOXES"))-SUM(($B$1:$B$3="FRUIT")*($C$1:$C$3="BOXES"))))}
    Unfortunately this gives a result of 3, when the desired result is 2.

    Can anyone help with a formula that works and/or explain what is going wrong here? Any assistance would be greatly appreciated!

    --Jason--

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    try

    =MAX(SUMPRODUCT((A1:A3="Deliver")*(B1:B3="Fruit")),SUMPRODUCT((A1:A3="Deliver")*(C1:C3="Boxes")))

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's a very clever way of looking at it -- and thank you for the very speedy reply! I've never seen the SUMPRODUCT formula used with that sort of format either (although admittedly my only experience with that particular formula is having read Excel 2002's help topic on it earlier today).

    Unfortunately that formula doesn't quite hit the mark. Try putting in the following data and you'll see what I mean --

    DELIVER FRUIT BAGS
    DELIVER VEGETABLES BOXES
    DELIVER FRUIT BAGS

    You'll get a result of '2' instead of a result of '3', as your formula is looking at columns B and C independently of each other and just picking the one that has the most matches of either "FRUIT" or "BOXES"...

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ah-ha! With your help, I've got it...
    All it needs is this --
    =SUMPRODUCT((A1:A4="Deliver")*((B1:B4="Fruit")+(C1:C4="Boxes")))

    Thanks HEAPS. I really owe you one!

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    You may prefer
    Array Formula

    =SUM(OR(C1:C3="Boxes")*(B1:B3="Fruit")*(A1:A3="Deliver"))

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    And it seems I can simply use "SUM" instead of "SUMPRODUCT" to the same result, with the array formula --

    {=SUM((A1:A3="Deliver")*((B1:B3="Fruit")+(C1:C3="Boxes")))}

    Thanks again!

User Tag List

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
  •  

 

 
DMCA.com