SUM up multiple fields based on Criteria

  • Thread starter Thread starter Andi
  • Start date Start date
A

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
 
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.
 
Something like this
=sumproduct(--(A1:A5="Y"),B1:B5)+sumproduct(--(c1:c5="Y"),D1:D5)

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
 
Back
Top