sumif problem

[bemiller]

I need to sum a column when the quantities meet two criteria. The name in one column, and the date in another column. Probably an easy formula, but new at this and having trouble.

[JBeaucaire]

The most common technique would probably be a SUMPRODUCT() formula in this layout:

=SUMPRODUCT(--(RangeOfNames=CellWithName),--(RangeOfDates=CellWithDate),RangeOfValues)

If the Names are A1:A100
Date are B1:B100
Values to sum are C1:C100

Name you want to check is in F1
Date you want to check in in F2

The formula would be:
=SUMPRODUCT(--(A1:A100=F1),--(B1:B100=F2),C1:C100)


'Complete explanation of SUMPRODUCT
http://www.xldynamic.com/source/xld....T.html#classic

[bemiller]

Thank you! The previous formula given did not have two dashes. Made a world of difference.

[JBeaucaire]

Yeah, the -- causes all the TRUE answers in each bracketed section to return a value of 1, so you get an array of {0;1;1} type answers for one group, then another array of {1;0;1} answers for the next group...then the actual values for the third array that has no comparison cell.

SUMPRODUCT then tries to multiply the values together.

{0;1;1} x {1;0;1} x {50,25,60}

The first answer in each array equates to: 0 x 1 x 50 = 0...so this isn't a match.
The second answer is 1 x 0 x 25 = 0 also not a match.

Only when BOTH arrays return a 1 in the same position do you get 1 x 1 x 60 = 60 ...a match! That would continue to add the sums up of the matching rows...just like a SUMIF would do for one criteria.

NOTE: Without the third array, the two --() sections will function like a COUNTIF() formula...only the sets that match BOTH/ALL criteria will result in a {1 x 1} or {1 x 1 x 1} result and those would be added together. So SUMPRODUCT() makes a great "multi-condition-countif".
==========
If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].



(Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)

[Rob Moyle]

JBeaucaire... THANK YOU!

I've used SUMPRODUCT() before in a pure "Ok, let's try copying and pasting this then playing around with the bits..." sense, which is not my ideal way of working but as I didn't understand the formula properly it was all I could do to get by.

Your post has explained how it works much better than anything I've read elsewhere and how the dashes '--' effect the formula. The xlDynamic link is very helpful too.

Thanks heaps, and lookout SUMPRODUCT() here I come! (Counting/multiplying multiple criteria is something I have to do quite often these days, this going to save me a lot of heartache!).

You (and many of the other Gurus around here) are a legend!