Embedding Multiple Conditions from Neighbor Cells in SUMPRODUCT

[NicoloJ]

Hi all,
I have the following table below, where I would like to perform a SUMPRODUCT on column "F" and "G", with two conditions:
- The corresponding cell in "D" column has to be empty
- The corresponding cell in "E" has to contain the string ".TO"

and an other SOMPRODUCT, but with corresponding cell in "E" not containing ".TO"....

So the answer should be:
- First formula : 210*47.68 = 10,012.8
- Second formula: 21*493.36 + 47*221.75 = 20,782.81

I am unable to get to the result, always having errors when I try to embed if statements...
Please let me know if you find the solution, and if it is possible.

 

[Fluff]

Hi & welcome to MrExcel.
If the .TO is always the last 3 characters, try

=SUMPRODUCT((D2:D10="")*(RIGHT(E2:E10,3)=".TO"),F2:F10,G2:G10)
=SUMPRODUCT((D2:D10="")*(RIGHT(E2:E10,3)<>".TO"),F2:F10,G2:G10)

 

[NicoloJ]

Hi & welcome to MrExcel.
If the .TO is always the last 3 characters, try

=SUMPRODUCT((D2:D10="")*(RIGHT(E2:E10,3)=".TO"),F2:F10,G2:G10)
=SUMPRODUCT((D2:D10="")*(RIGHT(E2:E10,3)<>".TO"),F2:F10,G2:G10)

Hi, it works wonderful!
Thanks a lot.

 

[Fluff]

You're welcome & thanks for the feedback.