SUMIFS

[MurrayBarn]

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

 

[Bernard Liengme]

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(D17,C1:C7,G1,B1:B7,H1)
Of course the helper column could be hidden or placed elsewhere

best wishes

 

[Bassman62]

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.SUMPRODUCT.html#examples
Hope this helps.

Regards
Dave

 

[T. Valko]

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

 

[MurrayBarn]

Thank you to all the above

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


See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html