Excel Sum with Multiple Conditions

[jwpiii]

Need to do a sum with multiple conditions. 1st condition is column W equals "Open" then second condition is a calculation of Column F is date is 20 days or more before todays date. I need to sum column E for those totals in which both conditions are true. My range is row 3 through row 500 for each.

Row Column E Column F Column W
# Amount Funded Date Funded Status

3 100,000.00 12-3-02 Pending
4 85,000.00 2-10-03 Open
5 65,000.00 1-15-03 Open
6 110,000.00 12-15-02 Open

Using 2-13-03 as todays date, My total should be $175,000. Since the date of row 4 is 2-10-03 it should be excluded from my calculation. Row 3 should also be excluded because status is Pending and not Open.

 

[DaleWatson123321]

jwpiii,

One of the "ideal" types of formulas to use in situations of multiple criteria, is Excel's "database" formulas - =DSUM in your particular case, but there are others like =DCOUNTA, =DMIN, =DMAX, =DAVERAGE, etc.

Two options for you (your choice)...

1) I can create an example based on the data you've provided.

2) You could, if you prefer, email me your file, and I'll create the formulas in your file and return it.

Excel's database formulas are EXTREMELY powerful, and an added bonus, is that using the same criteria for the formulas, one can easily extract the "records behind any formula" - for generating "detail" reports.

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JoyInOK]

Insert a new column with the following formula in Row 3
=IF(((TODAY()-E3)>20),IF(W3 = "OPEN",B2,0)) and copy it for each row. This will give you a column with numbers only when both conditions are met. You can then sum them at the bottom of the column.

 

[DaleWatson123321]

JoyInOk,

An "assist" for you... I believe you meant to use the following...

=IF(((TODAY()-F3)>20),IF(W3 = "OPEN",E2,0))

While solutions like yours are indeed "workable" and perhaps in certain situations are quite acceptable, my primary concern is that there are MANY, MANY Excel users who have been "short changed" by Microsoft's NOT having focused attention on the EXTREME power of Excel's "database functions".

jwpiii's example is a fairly simple example. However, there are often many situations with database lists, where there's a need for more in-depth analysis and reporting of the data. By beginning to use this VERY powerful component of Excel, it will "open the door" WIDE open to the ability to generate such reports that require in-depth analysis of data.

If you would like, I can also email you an example or two of what I'm referring to.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[jwpiii]

Thanks for the help, here is the formula that I am using currently. Are there any draw backs to it's usage?

=SUM(IF(W3:W500=&quot;Open&quot;,IF(F3:F500<=(TODAY()-AA19),E3:E500,0),0))

In the formula, AA19 contains the number of days to base the calculation on per user input.

 

[JoyInOK]

DaleWatson123321, sure that would be great. I currently do fairly basic Excel tasks, but will in the future be automating some Access-to-Excel data exchange, and that may be quite helpful.
adkinsj@odawan.net

 

[xlbo]

As always, here is an ARRAY formula alternative to Dale's DATABASE formulae examples. Please note that depending on the usage / number of formulae, either way could work for you:
=SUM(($W$3:$W$500=&quot;Open&quot*($F$3:$F$500<=Today()-20)*($E$3:$E$500))

Use CTRL+SHIFT+ENTER to enter the formula (as opposed to just ENTER) You will see { } on either side of the formula if you have done this correctly Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[debdas]

You can use SUMIF condition

 

[GlennUK]

errm ... debdas, you can't use SUMIF with multiple conditions.

Glenn.

 

[xlbo]

but you can use SUMPRODUCT (just to cloud the issue further ;-) ) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Hasit]

An option that refes to the array formula solution. Use the &quot;CONDITIONAL SUM&quot; wizard, which will build the formula for you, so you don't have to worry about the details of the formula.