tricky multi-condition counting

jasonconlon

Board Regular
Joined
Mar 14, 2002
Messages
80
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--
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
try

=MAX(SUMPRODUCT((A1:A3="Deliver")*(B1:B3="Fruit")),SUMPRODUCT((A1:A3="Deliver")*(C1:C3="Boxes")))
 
Upvote 0
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"...
 
Upvote 0
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!
 
Upvote 0
You may prefer
Array Formula

=SUM(OR(C1:C3="Boxes")*(B1:B3="Fruit")*(A1:A3="Deliver"))
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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