SUMIF? – Sum numbers if a condition is met

Topic

New Reply

My worksheet has a column containing the amounts of checks received for deposit in the bank, and a column containing the dates the checks were deposited. If a check has been deposited, its amount is in the amounts column and there is a date in the corresponding dates column. If a check has been received but not yet deposited, its amount is in the amounts column, but its dates column is blank.

My objective is to calculate the sum of the amounts for checks that have actually been deposited.

Range F10:F99, named ChecksReceived, contains blanks or the amounts of the checks received. Range I10:I99, named DatesOfDeposts, contains blanks or the dates of the deposits.

I’ve tried various forms of the SUMIF function, but nothing has worked. Descriptions of SUMIF are pretty sketch, IMHO, but I’m thinking that SUMIF isn’t the solution to my need.

Reply | Quote

Replies

[PHP]=SUMPRODUCT(ChecksReceived*(DatesOfDeposits””))[/PHP]

will accomplish what you want.

You could improve this by testing for an actual date in DatesOfDeposits rather than simply for a blank cell.

Reply | Quote

I have no idea why the line break in my post is appearing after the first “(“. I tried editing my post, only find that more unwanted line breaks mysteriously appeared, so I gave up

Anyway, I hope it makes sense “as is”.

Reply | Quote

Martin,

To prevent weird spacing include your formula with in [noparse]

Code:

 formula here 

[/noparse] tags, it also makes them easier to copy. :cheers:

Code:

=SUMPRODUCT([COLOR=#333333][FONT font=Arial]ChecksReceived*([/FONT][/COLOR]DatesOfDeposits""))

BTW you can also use noparse tags which follow the same convention, I actually used these to get the code tags to show above.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I tried CODE tags and still got unwanted line breaks !

PHP tags worked though.

Strange.

Reply | Quote

I saw no unwanted line breaks, earlier or now.

Anyway, the code provided works perfectly. The SUMPRODUCT function has been the key to my last two Excel mysteries. I guess I need to read up on it!

I guess it works if the expression is TRUE, which equates to 1. If the expression is FALSE, I guess it equates to zero.

Many, many years ago when I was a BASIC programmer, TRUE equated to -1

Reply | Quote

“

I guess it works if the expression is TRUE, which equates to 1. If the expression is FALSE, I guess it equates to zero.”

Quite so – in this instance SUMPRODUCT multiplies the amount by the value of the truth test: true (x 1) adds the amount into the total, false (x 0) adds nothing.

Its a very powerful function, allowing up to 30 tests to be applied.

Reply | Quote

Just as an additional to this already solved problem.
You could use SUMIF like this

Code:

=SUMIF(DatesOfDeposits,"",ChecksReceived)

This gives the same result, unless I have misread the question.

SUMPRODUCT is a wonderful function, especially with 2 conditions or more in older versions of Excel.
But SUMIF has been optimised for the calculation whereas (and I may well be corrected on this),
SUMPRODUCT is a volatile function.
This means that it recalculates even if none of the cells effecting it are changed.

On a bigger spreadsheet, this can have an impact on performance.

Feel free to shoot me down in flames on that one. :rolleyes:

Reply | Quote

SUMPRODUCT is not volatile.

Reply | Quote

. . . and, curiously, SUMIF can be volatile !

http://www.decisionmodels.com/calcsecretsi.htm

One particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions:[/FONT][/COLOR]
=SUMIF(A1:A4,”>0″,B1) is volatile whereas =SUMIF(A1:A4,”>0″,B1:B4) is not volatile. Both of these formulae will reference cells B1:B4.
Presumably this volatile behaviour was added to give correct results when B2:B4 were changed. [/FONT][/COLOR]

Reply | Quote

For us programming plebes on the lower rungs of the excel formulas/programming food chain an example or sample worksheet would prove to be very instructive!!

Regards,
Marty

Reply | Quote

What is it that you’d like an example of ? Sorry if I am being stupid :huh:

Reply | Quote

Hey Martin,

an excel sheet with data and the subject formula or code showing the results, makes for an easy reference when searching for a solution…thanks for your response…

Marty

Reply | Quote

Marty,

A sample file showing a simple use of SUMPRODUCT is attached.

Enter your dates in the two pale yellow cells and the cell to the right will show the sum of the amounts that fall between (or on) those dates.

You can add more conditions inside the SUMPRODUCT function, up to a total of 30.

The worksheet is protected to prevent you overtyping the formulae but there’s no password set.

Hope this helps.

NB If you set the Start Date to be after the Finish Date, you will get the result you deserve :rolleyes:

Reply | Quote

Martin,

a little slow on the thanks…working toooo much, but thank you!!

Marty

Reply | Quote