SUM up multiple fields based on Criteria

[Andi]

I need to create a formula that will find a "Y" value in multiple fields and
then find the Monetary value associated with these fields and then give a
total.

Example, If there is a Y under Lab and MedHx, find the cost for each on the
Budget sheet, then total the values in Visit Cost.

Is this possible?

Thanks

 

[sheryarkhan]

Hi!
i'm unable to understand your question may be this will help you.

If you are using excel 2007 then use sumifs.

Suppose your Lab & MedH data start from ( f20:g26) and you want to sum
(b3:b9) on budget sheet then try

=SUMIFS(BudgetSheet!$B$3:$B$9,$F$20:$F$26,"y",$G$20:$G$26,"y")+SUMIFS(BudgetSheet!$C$3:$C$9,$G$20:$G$26,"y",$F$20:$F$26,"y")

if you are not using 2007 then try sumproduct approach

=SUMPRODUCT(((F20:F25="Y")*(G20:G25="Y")*(BudgetSheet!B3:B8))+((F20:F25="Y")*(G20:G25="Y")*(BudgetSheet!C3:C8)))


adjust references accordingly.

 

[Paul C]

Something like this
=sumproduct(--(A1:A5="Y"),B1:B5)+sumproduct(--(c1:c5="Y"),D15)

in this simple example the Y/N values are in columns A and C and the
corresponding data in columns B and D

when using the --(A1:A5="Y") condition true values are 1 and false values
are 0, so this sums only what you want.

Change the ranges as needed (can be on other sheets)
like this =SUMPRODUCT(--(Sheet2!B2:B3="Y"),Sheet3!C2:C3)

This should do the trick

 

[Andi]

Thanks.

Both the SUMProduct and SUMIF solutions here work for me.