| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using if for multiple criteria
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))+(I F(J2="w",C2,D2))) Thanking you Anne |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
Assume that C2 will *always* be larger then D2:
=COUNTIF(E2:J2,"w")*(C2-D2)+D2*6 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "enna49" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
This is a bit neater
=SUMPRODUCT((E2:J2="W")*C2)+SUMPRODUCT((E2:J2<"W" )*D2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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))+(I F(J2="w",C2,D2))) Thanking you Anne |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
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 "Ragdyer" wrote: Assume that C2 will *always* be larger then D2: =COUNTIF(E2:J2,"w")*(C2-D2)+D2*6 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "enna49" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
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" wrote: This is a bit neater =SUMPRODUCT((E2:J2="W")*C2)+SUMPRODUCT((E2:J2<"W" )*D2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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))+(I F(J2="w",C2,D2))) Thanking you Anne |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
Please expand. If E2:J2 = "B" what is to happen to the sum/
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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" wrote: This is a bit neater =SUMPRODUCT((E2:J2="W")*C2)+SUMPRODUCT((E2:J2<"W" )*D2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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))+(I F(J2="w",C2,D2))) Thanking you Anne |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
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 "Bernard Liengme" wrote: Please expand. If E2:J2 = "B" what is to happen to the sum/ best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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" wrote: This is a bit neater =SUMPRODUCT((E2:J2="W")*C2)+SUMPRODUCT((E2:J2<"W" )*D2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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))+(I F(J2="w",C2,D2))) Thanking you Anne |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "enna49" wrote in message ... 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 "Ragdyer" wrote: Assume that C2 will *always* be larger then D2: =COUNTIF(E2:J2,"w")*(C2-D2)+D2*6 -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "enna49" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
How about
=SUMPRODUCT((F2:K2="W")*C2)+SUMPRODUCT((F2:K2="I") *D2)+SUMPRODUCT((F2:K2="B")*E2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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 "Bernard Liengme" wrote: Please expand. If E2:J2 = "B" what is to happen to the sum/ best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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" wrote: This is a bit neater =SUMPRODUCT((E2:J2="W")*C2)+SUMPRODUCT((E2:J2<"W" )*D2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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))+(I F(J2="w",C2,D2))) Thanking you Anne |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
Thank you works perfectly. Having a bit of mushy brain is me
"Bernard Liengme" wrote: How about =SUMPRODUCT((F2:K2="W")*C2)+SUMPRODUCT((F2:K2="I") *D2)+SUMPRODUCT((F2:K2="B")*E2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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 "Bernard Liengme" wrote: Please expand. If E2:J2 = "B" what is to happen to the sum/ best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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" wrote: This is a bit neater =SUMPRODUCT((E2:J2="W")*C2)+SUMPRODUCT((E2:J2<"W" )*D2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "enna49" wrote in message ... 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))+(I F(J2="w",C2,D2))) Thanking you Anne |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
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 "Ragdyer" wrote: 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "enna49" wrote in message ... 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 "Ragdyer" wrote: Assume that C2 will *always* be larger then D2: =COUNTIF(E2:J2,"w")*(C2-D2)+D2*6 -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "enna49" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using if for multiple criteria
Thanks for the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "enna49" wrote in message ... 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 "Ragdyer" wrote: 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. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "enna49" wrote in message ... 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 "Ragdyer" wrote: Assume that C2 will *always* be larger then D2: =COUNTIF(E2:J2,"w")*(C2-D2)+D2*6 -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "enna49" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
| match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
| Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
