Help with sumproduct

[Sean15]

Hello:

Re: =SUMPRODUCT(--(J1:J5000="OPS*"),--(N1:N5000="Turnover"),Q1:Q5000)

The above is returning zero but when I change OPS to a specific value, formula returns the sum of specific values. The problem is my OPS range contains several variation of OPS (eg OPS51, OPS55, OPS56) and I need to get the sum of all OPS and Turnover so I can't use a specific value. Could you help me with formula.

Many thanks for your help.


Sean

 

[Jonmo1]

Yes, sumproduct doesn't like wildcards like that...
Try using LEFT

=SUMPRODUCT(--(LEFT(J1:J5000,3)="OPS"),--(N1:N5000="Turnover"),Q1:Q5000)

 

[Sean15]

Thank you so much jonmo1.

I have to ask, what will we do if this site ever goes down?

Sean.

 

[Jonmo1]

Thank you so much jonmo1.

I have to ask, what will we do if this site ever goes down?

Sean.

Bite your tounge!!!!

 

[T. Valko]

Hello:

Re: =SUMPRODUCT(--(J1:J5000="OPS*"),--(N1:N5000="Turnover"),Q1:Q5000)

The above is returning zero but when I change OPS to a specific value, formula returns the sum of specific values. The problem is my OPS range contains several variation of OPS (eg OPS51, OPS55, OPS56) and I need to get the sum of all OPS and Turnover so I can't use a specific value. Could you help me with formula.

Many thanks for your help.


Sean

If you're using Excel 2007 or later...

=SUMIFS(Q1:Q5000,J1:J5000,"OPS*",N1:N5000,"Turnover")