Sumproduct with conditions - urgent help needed

[JANA]

I have a sumproduct in my spreadsheet =sumproduct(N8:N13,I8:I13) that works
fine. However, I need to add a condition to this. If F8:F13="XYZ". How do
I change the sumproduct to only calculate if column F contains "XYZ"?

Your help would be greatly appreciated - under a tight deadline for a client!
Jana

 

[T. Valko]

Try this:

=SUMPRODUCT(--(F8:F13="XYZ"),I8:I13,N8:N13)

 

[Venkatesan]

Hope this works

=if(sum(F8:F13)=xyz,sum(F8:F13),(Sum(F8:F13,I8:I13)))
Thanks
Venkatesan

 

[David Biddulph]

No it won't work, Venkatesan.

You snipped the question, which was:
"I have a sumproduct in my spreadsheet =sumproduct(N8:N13,I8:I13) that works
fine. However, I need to add a condition to this. If F8:F13="XYZ". How do
I change the sumproduct to only calculate if column F contains "XYZ"?"

There are a number of things wrong with your answer:

Firstly, if you want to compare with a text string like XYZ you need to
enclose it in double quotes.

Secondly a SUM() formula will return a number, not a text string, so your IF
test would always fail.

Thirdly, the OP wanted an answer which was not a SUM of columns F and I, but
a SUMPRODUCT of columns N and I.

Fourthly, the OP wanted the SUMPRODUCT calculated for the rows where column
F contains "xyz", and not for the other rows.

Fifthly, and less important, you don't need parentheses around your final
SUM() function.

The answer which the OP needed is either
=SUMPRODUCT(--(F8:F13="xyz"),N8:N13,I8:I13) or
=SUMPRODUCT((F8:F13="xyz")*N8:N13*I8:I13)