sumif

[Guest]

Here's the data:

A B C
TOTAL IMPULSE 100.00
TOTAL IMPULSE 100.00
TOTAL IMPULSE 100.00
TOTAL DIRECT 200.00
TOTAL DIRECT 200.00
TOTAL DIRECT 200.00

I need to sum C for either TOTAL, or IMPULSE, or DIRECT

Started using a sumif where =sumif(A2:C6,A13,C2:C6) and A13 = can either be
TOTAL, IMPULE, DIRECT.

When A13 = TOTAL the sumif works (total=900). When A13 = IMPULSE or DIRECT
the sumif does not work.

Please help

 

[Guest]

=SUMIF($B$1:$B$6,A13,$C$1:$C$6)

where A13 =IMPULSE or A13=DIRECT works OK for me.

 

[Herbert Seidenberg]

If you enter a 2D array (A2:C6) into SUMIF(), it will use
only the first column of that array, A2:A6.
If you want the formula to say: "Look in either column A or B"
then you have to use an OR function.
SUMIF does not accept an OR function.
SUMPRODUCT is formulated as an AND function,
but you can convert it to an OR function by using DeMorgan's theorem.
=SUMPRODUCT(NOT(NOT(A2:A6=A13)*NOT(B2:B6=A13))*C2:C6) or
=SUMPRODUCT(NOT((A2:A6<>A13)*(B2:B6<>A13))*C2:C6)

 

[Guest]

Thanks, but the "NOT" is throwing me.

 

[Herbert Seidenberg]

NOT(TRUE)=FALSE
NOT(FALSE)=TRUE
NOT(1)=0
NOT(0)=1
or see
http://www.ee.surrey.ac.uk/Projects/Labview/boolalgebra/

 

[Herbert Seidenberg]

If you use Pivot Table, you don't need any formulas whatsoever.
Assume you data looks like this:

ListA ListB Qty
Atot DIR 380
Atot DIR 340
Atot IMP 180
Atot IMP 150
Atot IMP 330
Atot IMP 400
Btot IMP 470
Btot IMP 300
Btot DIR 170
Btot DIR 430

The Pivot Table will give you all the possible sums:

DIR IMP Total
Atot 720 1060 1780
Btot 600 770 1370
Total 1320 1830 3150

Use ListA as Row and ListB as Column

 

[Guest]

Thanks Herbert.

I agree, a Pivot Table would have been the easist but the End Users would
"freak out". To big of a learning curve.

What I actually ended up doing was

=SUMPRODUCT(--(A1:A6=A13)--(B1:B6=A13),C1:C6)

This proved to be the most effective. I could easily change A13 and it
would calculate perfectly.

However, I still do not understand what "--" does or means, it just works

 

[Herbert Seidenberg]

-TRUE=-1
-(-TRUE)=--TRUE=+TRUE=1
--(FALSE)=0
So -- just converts a TRUE/FALSE to 1/0
Concatenated, they add this way:
--TRUE--FALSE=1+0=1
--FALSE--TRUE=0+1=1
--TRUE--TRUE=1+1=2
This works out in your case, since only a single column
will contain TRUEs. In a general case, you could end up
with a multiplier of 2 or more.