Is SUMPRODUCT the right function to use?
 

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")+SU MPRODUCT(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?

Is SUMPRODUCT the right function to use?
 

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
--
Regards
Roger Govier



"Twishlist" wrote in message
...
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")+SU MPRODUCT(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?

Is SUMPRODUCT the right function to use?
 

=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



"Twishlist" wrote in message
...
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")+SU MPRODUCT(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?

Is SUMPRODUCT the right function to use?
 

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

"Roger Govier" wrote:

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
--
Regards
Roger Govier



"Twishlist" wrote in message
...
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")+SU MPRODUCT(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?

Is SUMPRODUCT the right function to use?
 

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

"Bernie Deitrick" wrote:

=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



"Twishlist" wrote in message
...
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")+SU MPRODUCT(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?