IF statement with nested SUMPRODUCT?

B

Bazael

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.
 
M

Marcelo

=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:
 
B

Bazael

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!
 
M

Marcelo

yaw

glad to help
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Bazael" escreveu:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Summing a range of cells based on criteria in another range 2
#VALUE problem 4
Forecast v actual - row addition problem 2
How to do an IF statement that copy data based on a partial match 0
formula 2
Sumproduct or Other Formula? 2
How to refer to current column in a formula? 4
Help requested for nested conditional formulas referencing other c 3

Top