Is SUMPRODUCT the right function to use?

[Guest]

I need help with a formula to add a result based on multiple criteria: If a
particular criteria (equal to L14) is met in c2:c1180, then check J2:j1180
for items starting with "m", and/or "n" and add the total quantities
together.
I know that the answer should be 7, as the countif function has told me that
the criteria in L14 shows up 7 times in C2:c1180.
I've attempted the following formula, but it returns a 3, which is equal to
"m" only.
=SUMPRODUCT((C2:C1180=L14)*(LEFT(J2:J1180)="m")+SUMPRODUCT(C2:C1180=L14)*(LEFT(J2:J1180)="d"))
I would really welcome assistance to advance my comprehension of the
complexities of formula writing.
Secondly, what specifically does the -- mean?

 

[Roger Govier]

Hi

you would need to change that form of the formula to
=SUMPRODUCT((C2:C1180=L14)*(LEFT(J2:J1180)="m"))+
SUMPRODUCT((C2:C1180=L14)*(LEFT(J2:J1180)="d"))

You only had an outer set of parentheses to the overall formula, as opposed
to each Sumproduct function

Alternatively, you could use
=SUMPRODUCT((C2:C1180=L14)*(LEFT(J2:J1180)={"m","d"}))

For more help on Sumproduct, including what the double unary minus -- does,
take a look at Bob Phillips excellent discussion document at

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Bernie Deitrick]

=SUMPRODUCT((C2:C1180=L14)*(LEFT(J2:J1180,1)="m")) +
SUMPRODUCT((C2:C1180=L14)*(LEFT(J2:J1180,1)="n"))

The -- is a double unary operation that can convert an array of TRUE / FALSE
values to numerics. It isn't really needed with the way you have built your
formulas, with each multiplying two arrays of TRUE / FALSE values, which
will convert the results to numeric values.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thank you so much!...I'll now go and check out the xldynamic website for
other useful info.

 

[Guest]

It's invaluable having assistance from people such as yourselves who know the
product so thoroughly...thanks heaps.