Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS
A B C D
1 01/11/2008 Motor -10,039.24 -1,232.89 2 01/11/2008 Non-Motor -12,414.70 -1,524.61 3 01/12/2008 SASRIA Mo -52.75 -6.48 4 01/12/2008 SASRIA No -712.15 -87.46 5 01/01/2009 Motor 10,039.24 1,232.89 6 01/01/2009 Non-Motor 12,414.70 1,524.61 7 01/01/2009 SASRIA Mo 52.75 6.48 I really want to use the SUMIFS formula to sum columns C and D for all items that fall in a specific month like November 2008, December 2008 etc AND that correspond to a category per column B like Motor. I really battling with the syntax I have a solution using SUMPRODUCT but it uses double negatives which I dont understand. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS
Cannot be done with SUMIFS, can be done with SUMPRODUCT
With "Motor" in F1 and the number 11 in G1 =SUMPRODUCT(--($B$1:$B$7=F1),--(MONTH($A$1:$A$7)=G1),$C$1:$C$7) If you are happy with adding a helper column in B with =MONTH(A1), then you could use =SUMIFS(D1:D7,C1:C7,G1,B1:B7,H1) Of course the helper column could be hidden or placed elsewhere best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "MurrayBarn" wrote in message ... A B C D 1 01/11/2008 Motor -10,039.24 -1,232.89 2 01/11/2008 Non-Motor -12,414.70 -1,524.61 3 01/12/2008 SASRIA Mo -52.75 -6.48 4 01/12/2008 SASRIA No -712.15 -87.46 5 01/01/2009 Motor 10,039.24 1,232.89 6 01/01/2009 Non-Motor 12,414.70 1,524.61 7 01/01/2009 SASRIA Mo 52.75 6.48 I really want to use the SUMIFS formula to sum columns C and D for all items that fall in a specific month like November 2008, December 2008 etc AND that correspond to a category per column B like Motor. I really battling with the syntax I have a solution using SUMPRODUCT but it uses double negatives which I dont understand. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS
Hello,
SUMPRODUCT multiplies corresponding components in arrays with the same dimensions, and returns the sum of those products. In some cases, like yours, where each cell in an array is being compared with another value, the items may result as TRUE or FALSE. However SUMPRODUCT cannont multiply these results so by using the double unary operator "--" you coerce Excel to convert the logical "TRUE or FALSE" results of your arrarys into numeric 1 or 0 which is required for the operation. Try building the formula using the function wizard (fx). By seeing the resulting arrays it may help you understand what is happening inside the formula. Go here to get more information. http://www.xldynamic.com/source/xld.....html#examples Hope this helps. Regards Dave "MurrayBarn" wrote in message ... A B C D 1 01/11/2008 Motor -10,039.24 -1,232.89 2 01/11/2008 Non-Motor -12,414.70 -1,524.61 3 01/12/2008 SASRIA Mo -52.75 -6.48 4 01/12/2008 SASRIA No -712.15 -87.46 5 01/01/2009 Motor 10,039.24 1,232.89 6 01/01/2009 Non-Motor 12,414.70 1,524.61 7 01/01/2009 SASRIA Mo 52.75 6.48 I really want to use the SUMIFS formula to sum columns C and D for all items that fall in a specific month like November 2008, December 2008 etc AND that correspond to a category per column B like Motor. I really battling with the syntax I have a solution using SUMPRODUCT but it uses double negatives which I dont understand. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS
As Bernard noted, you can't use SUMIFS for something like this. SUMIFS does
"straight" comparrisons. You can't "manipulate" an array like you're trying to do by testing for the month: MONTH(B1:B10)=n I have a solution using SUMPRODUCT but it uses double negatives which I dont understand. See this: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "MurrayBarn" wrote in message ... A B C D 1 01/11/2008 Motor -10,039.24 -1,232.89 2 01/11/2008 Non-Motor -12,414.70 -1,524.61 3 01/12/2008 SASRIA Mo -52.75 -6.48 4 01/12/2008 SASRIA No -712.15 -87.46 5 01/01/2009 Motor 10,039.24 1,232.89 6 01/01/2009 Non-Motor 12,414.70 1,524.61 7 01/01/2009 SASRIA Mo 52.75 6.48 I really want to use the SUMIFS formula to sum columns C and D for all items that fall in a specific month like November 2008, December 2008 etc AND that correspond to a category per column B like Motor. I really battling with the syntax I have a solution using SUMPRODUCT but it uses double negatives which I dont understand. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS
Thank you to all the above
"T. Valko" wrote: As Bernard noted, you can't use SUMIFS for something like this. SUMIFS does "straight" comparrisons. You can't "manipulate" an array like you're trying to do by testing for the month: MONTH(B1:B10)=n I have a solution using SUMPRODUCT but it uses double negatives which I dont understand. See this: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "MurrayBarn" wrote in message ... A B C D 1 01/11/2008 Motor -10,039.24 -1,232.89 2 01/11/2008 Non-Motor -12,414.70 -1,524.61 3 01/12/2008 SASRIA Mo -52.75 -6.48 4 01/12/2008 SASRIA No -712.15 -87.46 5 01/01/2009 Motor 10,039.24 1,232.89 6 01/01/2009 Non-Motor 12,414.70 1,524.61 7 01/01/2009 SASRIA Mo 52.75 6.48 I really want to use the SUMIFS formula to sum columns C and D for all items that fall in a specific month like November 2008, December 2008 etc AND that correspond to a category per column B like Motor. I really battling with the syntax I have a solution using SUMPRODUCT but it uses double negatives which I dont understand. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumifs & Not Like | Excel Discussion (Misc queries) | |||
Sumifs | Excel Discussion (Misc queries) | |||
SUMIFS and OR | Excel Discussion (Misc queries) | |||
SUMIFS | Excel Discussion (Misc queries) | |||
SUMIFS and OR | Excel Worksheet Functions |