tricky multi-condition counting

[jasonconlon]

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

[Dave Patton]

try

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

[jasonconlon]

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

[jasonconlon]

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!

[Dave Patton]

You may prefer
Array Formula

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

[jasonconlon]

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!