|
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. |
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. |
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. |
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. |
| All times are GMT +1. The time now is 06:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com