Conditional Formulas

[MHSGal]

Hi community,
I'm stumped on conditional formulas. I'm trying to create a formula that
will do the following:
*Look at a particular column and if a cell says "Go" and if another cell in
a different column (on the same worksheet) says "Yes", then add up the dollar
values associated with the cells that meet both of those conditions. I've
tried sumif, and dsum and can not quite get it to work. Can someone please
lend some excel expertise. Is this even possible?

 

[Eduardo]

Hi,
I assume that you have Go in column G and yes in column H, then you want to
sum column I

=SUMPRODUCT(--(G6:G20="GO"),--(H6:H20="YES"),I6:I20)

change range to fit your needs, all ranges has to be the same among the
formula

 

[MHSGal]

Thank you Eduardo!!

Quick question, your response makes perfect sense. Would my formula change
if I'm trying to return the sum in one sheet and retrieve the data from
columns g and h for another worksheet?

 

[Eduardo]

If I didn't missunderstand you have your data in sheet 2 and you want to have
the sum in sheet 1 just you need to enter the sheet name as follow

=SUMPRODUCT(--(sheet2!G6:G20="GO"),--(sheet2!H6:H20="YES"),sheet2!I6:I20)

change the sheet name for yours

 

[MHSGal]

Thanks Eduardo...I'm still having syntax errors, but I get the idea behind
sumproduct and I'm confident that I can figure it out.

 

[Ashish Mathur]

Hi,

Try this

1. Suppose data is in range A2:C500. Row headings, say heading1, heading2
and numbers are in A1:C1
2. In D1:F1, type heading1, heading2 and numbers
3. In D2, type Go and in E2, type Yes
4. In F2, type =dsum($A$1:$C$500,F$1,D$1:E2)-sum(F$2:F2)

Please note that that I have subtracted sum($F2:F2) in the formula above to
take care of copying and pasting in case you need to ad more criteria in
D2:E2

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com