Multiple Conditional Sum

[joshuar]

Hi All,

I want to calculate the sum of a series in a row applying 2 conditions.
1. The corresponding column should not be blank.
2. The date in the corresponding column should not be greater than the current date.

A sample sheet is attached for reference.
Could anyone please guide?

Regards,
J

[teylyn]

Hi,

your data layout is not the best to facilitate this kind of calculation. Can it be assumed that you will add columns for new months as the year develops? You'll probably want a formula that will be portable to new columns as well?

On top of that, your spreadsheet contains several errors, e.g.

Total in AF
AF5 =L5+P5+P5+T5 -- you double add P5
AF6 =D6+H6+L6+P6+T6+X6++AB6 -- inconsistent formula: why would this one add hidden columns, whereas the one above does not, why the double ++ ?

You'd be best advised to enter your data in a simple data entry table with one record per row

Please Login or Register  to view this content.

and then use a pivot table to run break downs by month/supplier and other calculations. This would also facilitate the calculation of "unpaid including postdated" with a simple SUMIF() instead of a convoluted formula that is almost impossible to construct because of hidden columns.

Also, please update your profile and indicate which Excel version you use. Solutions may differ considerably between 2003 and 2007

cheers

[joshuar]

Hi Teylyn,

Thanks for the reply.

Sorry about the errors. I guess "haste makes waste".
I have changed the file.
Yes the original file has provisions for all months.
I am using excel 2007 version.
Is it possible to make the sum on the existing data or possibly I should use a helper cell that would return true/ false when the column is blank or the chq. is post dated and then try a sum.

Thanks,
J

[teylyn]

A formula can be constructed, hand picking the cells to compare, but when you add more columns for more months, you'll have to change and adjust the formula and it will take a lot of maintenance.

one possible formula for the three months visible is this

=SUM(IF(OR(O5>TODAY(),O5=""),L5,0),IF(OR(S5>TODAY(),S5=""),P5,0),IF(OR(W5>TODAY(),W5=""),T5,0))

but it's ugly and not dynamic, so it won't auto-adjust to more months when you insert new columns.