Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
=SUMIF($B$1:$B$6,A13,$C$1:$C$6) where A13 =IMPULSE or A13=DIRECT works OK for me. "Beagle" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
Thanks, but the "NOT" is throwing me.
"Herbert Seidenberg" wrote: 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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
NOT(TRUE)=FALSE
NOT(FALSE)=TRUE NOT(1)=0 NOT(0)=1 or see http://www.ee.surrey.ac.uk/Projects/...w/boolalgebra/ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
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" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
-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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Help with SUMIF function | Excel Worksheet Functions | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) |