| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement with nested SUMPRODUCT?
Any insight offered will be very appreicated.
I'm trying to use the following formula: =IF(AND(D8:D229="Labor CAP",C8:C229="Complete"),G8:G229,SUMPRODUCT(--(D8:D229="Labor CAP"),--(C8:C229<"Not Ordered"),E8:E229)) The goal is this: If any cells in range D8:D229 = Labor CAP and any cells in range C8:C229 = Complete, sum corresponding cells in range G8:G229, if not, find cells in range D8:D29 = Labor CAP and cells in range C8:C229 that are not equal to Not Ordered, and sum corresponding cells in range E8:E229. Thank you. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statement with nested SUMPRODUCT?
=if(sumproduct(--(D8:D229="Labor CAP")*(C8:c229="Complete"),(G8:G229))=0,sumproduct (--(c8:c229<"Not Ordered"),(E8:E229)),sumproduct(--(D8:D229="Labor CAP")*(C8:c229="Complete"),(G8:G229))) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Bazael" escreveu: Any insight offered will be very appreicated. I'm trying to use the following formula: =IF(AND(D8:D229="Labor CAP",C8:C229="Complete"),G8:G229,SUMPRODUCT(--(D8:D229="Labor CAP"),--(C8:C229<"Not Ordered"),E8:E229)) The goal is this: If any cells in range D8:D229 = Labor CAP and any cells in range C8:C229 = Complete, sum corresponding cells in range G8:G229, if not, find cells in range D8:D29 = Labor CAP and cells in range C8:C229 that are not equal to Not Ordered, and sum corresponding cells in range E8:E229. Thank you. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statement with nested SUMPRODUCT?
Thanks for the suggestion! The formula you proposed actually triggered me to
think of this: =SUMPRODUCT(--(D8:D229="Labor CAP"),--(C8:C229<"Not Ordered"),--(C8:C229<"Complete"),E8:E229)+(SUMPRODUCT(--(D8:D229="Labor CAP"),--(C8:C229<"Not Ordered"),--(C8:C229="Complete"),G8:G229)) And I was able to get the exact calculation I was looking for. Thanks again! "Marcelo" wrote: =if(sumproduct(--(D8:D229="Labor CAP")*(C8:c229="Complete"),(G8:G229))=0,sumproduct (--(c8:c229<"Not Ordered"),(E8:E229)),sumproduct(--(D8:D229="Labor CAP")*(C8:c229="Complete"),(G8:G229))) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Bazael" escreveu: Any insight offered will be very appreicated. I'm trying to use the following formula: =IF(AND(D8:D229="Labor CAP",C8:C229="Complete"),G8:G229,SUMPRODUCT(--(D8:D229="Labor CAP"),--(C8:C229<"Not Ordered"),E8:E229)) The goal is this: If any cells in range D8:D229 = Labor CAP and any cells in range C8:C229 = Complete, sum corresponding cells in range G8:G229, if not, find cells in range D8:D29 = Labor CAP and cells in range C8:C229 that are not equal to Not Ordered, and sum corresponding cells in range E8:E229. Thank you. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statement with nested SUMPRODUCT?
yaw
glad to help -- regards from Brazil Thanks in advance for your feedback. Marcelo "Bazael" escreveu: Thanks for the suggestion! The formula you proposed actually triggered me to think of this: =SUMPRODUCT(--(D8:D229="Labor CAP"),--(C8:C229<"Not Ordered"),--(C8:C229<"Complete"),E8:E229)+(SUMPRODUCT(--(D8:D229="Labor CAP"),--(C8:C229<"Not Ordered"),--(C8:C229="Complete"),G8:G229)) And I was able to get the exact calculation I was looking for. Thanks again! "Marcelo" wrote: =if(sumproduct(--(D8:D229="Labor CAP")*(C8:c229="Complete"),(G8:G229))=0,sumproduct (--(c8:c229<"Not Ordered"),(E8:E229)),sumproduct(--(D8:D229="Labor CAP")*(C8:c229="Complete"),(G8:G229))) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Bazael" escreveu: Any insight offered will be very appreicated. I'm trying to use the following formula: =IF(AND(D8:D229="Labor CAP",C8:C229="Complete"),G8:G229,SUMPRODUCT(--(D8:D229="Labor CAP"),--(C8:C229<"Not Ordered"),E8:E229)) The goal is this: If any cells in range D8:D229 = Labor CAP and any cells in range C8:C229 = Complete, sum corresponding cells in range G8:G229, if not, find cells in range D8:D29 = Labor CAP and cells in range C8:C229 that are not equal to Not Ordered, and sum corresponding cells in range E8:E229. Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested if statement ? | Excel Worksheet Functions | |||
| IF OR NESTED STATEMENT | Excel Discussion (Misc queries) | |||
| IF STATEMENT - NESTED | Excel Worksheet Functions | |||
| Nested if statement | Excel Worksheet Functions | |||
| Help with a nested IF statement. | Excel Discussion (Misc queries) | |||
Linear Mode
