using if for multiple criteria

[enna49]

Hi
I have used the formula below for adding across cells if certain criteria.
This does work, however is there a cleaner way of doing this. Example Data
below:

C2 = Working Shift Cost
D2 = Idle Shift Cost
E2 to J2 represents days of the week (Mon - Sat) and a Total is required for
this cost. Using Excel 2007

=SUM(IF(E2="W",C2,D2)+(IF(F2="w",C2,D2))+(IF(G2="w",C2,D2))+(IF(H2="w",C2,D2))+(IF(I2="w",C2,D2))+(IF(J2="w",C2,D2)))
Thanking you
Anne

 

[Ragdyer]

Assume that C2 will *always* be larger then D2:

=COUNTIF(E2:J2,"w")*(C2-D2)+D2*6

 

[Ragdyer]

Assume that C2 will *always* be larger then D2:

=COUNTIF(E2:J2,"w")*(C2-D2)+D2*6

 

[Bernard Liengme]

This is a bit neater
=SUMPRODUCT((E2:J2="W")*C2)+SUMPRODUCT((E2:J2<>"W")*D2)
best wishes

 

[Bernard Liengme]

This is a bit neater
=SUMPRODUCT((E2:J2="W")*C2)+SUMPRODUCT((E2:J2<>"W")*D2)
best wishes

 

[enna49]

Thank you for your quick response, this is great.

Please can you help in another issue that has arisen with the same scenario.

There is now an extra field required eg BREAKDOWN, how can I include this to
be another option.
I have also tried to fix the N/A option. If you know of a quick solution
that would also be helpful.

Thanking you

 

[enna49]

Thank you for your quick response, this is great.

Please can you help in another issue that has arisen with the same scenario.

There is now an extra field required eg BREAKDOWN, how can I include this to
be another option.
I have also tried to fix the N/A option. If you know of a quick solution
that would also be helpful.

Thanking you

 

[enna49]

Thank you Bernard

I tried SUMPRODUCT prior, now I see where I went wrong from your code.

I have added another request to this original: There is now another
Criteria required. eg BREAKDOWN "B". I know I should be able to add this,
but it is not working.
Any Help would be appreciated.
Thanks

 

[enna49]

Thank you Bernard

I tried SUMPRODUCT prior, now I see where I went wrong from your code.

I have added another request to this original: There is now another
Criteria required. eg BREAKDOWN "B". I know I should be able to add this,
but it is not working.
Any Help would be appreciated.
Thanks

 

[Bernard Liengme]

Please expand. If E2:J2 = "B" what is to happen to the sum/
best wishes

 

[Bernard Liengme]

Please expand. If E2:J2 = "B" what is to happen to the sum/
best wishes

 

[enna49]

Hi
The Breakdown cost is to be included in the Total
eg
C2 = Working Shift Cost
D2 = Idle Shift Cost
E2 = Breakdown
F2 to K2 represents days of the week (Mon - Sat) and a Total is required for
this cost. Using Excel 2007

Thanking you
Anne

 

[enna49]

Hi
The Breakdown cost is to be included in the Total
eg
C2 = Working Shift Cost
D2 = Idle Shift Cost
E2 = Breakdown
F2 to K2 represents days of the week (Mon - Sat) and a Total is required for
this cost. Using Excel 2007

Thanking you
Anne

 

[Ragdyer]

When do you apply the Breakdown cost to the total?
Is it added *only* when there is a "w"?

=COUNTIF(F2:K2,"w")*(C2+E2-D2)+D2*6

If not, you'll have to explain further.

 

[Ragdyer]

When do you apply the Breakdown cost to the total?
Is it added *only* when there is a "w"?

=COUNTIF(F2:K2,"w")*(C2+E2-D2)+D2*6

If not, you'll have to explain further.

 

[Bernard Liengme]

How about
=SUMPRODUCT((F2:K2="W")*C2)+SUMPRODUCT((F2:K2="I")*D2)+SUMPRODUCT((F2:K2="B")*E2)
best wishes

 

[Bernard Liengme]

How about
=SUMPRODUCT((F2:K2="W")*C2)+SUMPRODUCT((F2:K2="I")*D2)+SUMPRODUCT((F2:K2="B")*E2)
best wishes

 

[enna49]

Thank you works perfectly. Having a bit of mushy brain is me

 

[enna49]

Thank you works perfectly. Having a bit of mushy brain is me

 

[enna49]

Thanks for your response. I have the answer to my question, but now the
ball game has changed again, hopefully I can sort out myself with what
information I have received. Otherwise I will be calling on you assistance
again.
Thanking you
Anne