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