sumproduct for multiple criteria in different columns
 

I have been trying to use this site to run a sumproduct formula for the below:

A B C D
E F G
projid status targetedstartdate targetedcompldate amountbilled
Onsched.


I want to count how many times the instances of a number with the prefix
B00000 or BPR000000 occur in column A (called projid) and when "Late" occurs
in column G (called Onsched.)

There are 4300 rows of data.

my formula is:
=SUMPRODUCT(A2:A4300="B*")*(G2:G4300="Late")

I don't get a sum of the values. Any ideas?

sumproduct for multiple criteria in different columns
 

*Maybe* this:

=SUMPRODUCT(--(LEFT(A2:A4300)="B"),--(G2:G4300="Late"))

If there are entries other than B00000 or BPR000000 that start with "B" they
may be calculated. In that case we'll have to refine the condition.


--
Biff
Microsoft Excel MVP


"gbpg" wrote in message
...
I have been trying to use this site to run a sumproduct formula for the
below:

A B C D
E F G
projid status targetedstartdate targetedcompldate amountbilled
Onsched.


I want to count how many times the instances of a number with the prefix
B00000 or BPR000000 occur in column A (called projid) and when "Late"
occurs
in column G (called Onsched.)

There are 4300 rows of data.

my formula is:
=SUMPRODUCT(A2:A4300="B*")*(G2:G4300="Late")

I don't get a sum of the values. Any ideas?

sumproduct for multiple criteria in different columns
 

Try it this way...

=SUMPRODUCT((LEFT(A2:A43)="B")*(G2:G43="Late"))

Although it wouldn't make a difference to your result, you were missing a
set of parentheses around the multiplication of the two logical expressions,
so SUMPRODUCT took the first logical expression as its argument and
multiplied its result times the second logical expression (which, being
outside the SUMPRODUCT function, wouldn't be an array, I guess).

Rick


"gbpg" wrote in message
...
I have been trying to use this site to run a sumproduct formula for the
below:

A B C D
E F G
projid status targetedstartdate targetedcompldate amountbilled
Onsched.


I want to count how many times the instances of a number with the prefix
B00000 or BPR000000 occur in column A (called projid) and when "Late"
occurs
in column G (called Onsched.)

There are 4300 rows of data.

my formula is:
=SUMPRODUCT(A2:A4300="B*")*(G2:G4300="Late")

I don't get a sum of the values. Any ideas?

sumproduct for multiple criteria in different columns
 

they both returned the same value of 214 (after I changed Ricks 43 to 4309).
Thanks guys, I am still not sure why mine did not work. If I need to
substitute the B* for say BPR* would this still work?

"Rick Rothstein (MVP - VB)" wrote:

Try it this way...

=SUMPRODUCT((LEFT(A2:A43)="B")*(G2:G43="Late"))

Although it wouldn't make a difference to your result, you were missing a
set of parentheses around the multiplication of the two logical expressions,
so SUMPRODUCT took the first logical expression as its argument and
multiplied its result times the second logical expression (which, being
outside the SUMPRODUCT function, wouldn't be an array, I guess).

Rick


"gbpg" wrote in message
...
I have been trying to use this site to run a sumproduct formula for the
below:

A B C D
E F G
projid status targetedstartdate targetedcompldate amountbilled
Onsched.


I want to count how many times the instances of a number with the prefix
B00000 or BPR000000 occur in column A (called projid) and when "Late"
occurs
in column G (called Onsched.)

There are 4300 rows of data.

my formula is:
=SUMPRODUCT(A2:A4300="B*")*(G2:G4300="Late")

I don't get a sum of the values. Any ideas?

sumproduct for multiple criteria in different columns
 

In that case use:

=SUMPRODUCT(--(LEFT(A2:A4300,3)="BPR"),--(G2:G4300="Late"))

Or, use cells to hold the criteria:

J1 = B or BPR
K1 = Late

=SUMPRODUCT(--(LEFT(A2:A4300,LEN(J1))=J1),--(G2:G4300=K1))



--
Biff
Microsoft Excel MVP


"gbpg" wrote in message
...
they both returned the same value of 214 (after I changed Ricks 43 to
4309).
Thanks guys, I am still not sure why mine did not work. If I need to
substitute the B* for say BPR* would this still work?

"Rick Rothstein (MVP - VB)" wrote:

Try it this way...

=SUMPRODUCT((LEFT(A2:A43)="B")*(G2:G43="Late"))

Although it wouldn't make a difference to your result, you were missing a
set of parentheses around the multiplication of the two logical
expressions,
so SUMPRODUCT took the first logical expression as its argument and
multiplied its result times the second logical expression (which, being
outside the SUMPRODUCT function, wouldn't be an array, I guess).

Rick


"gbpg" wrote in message
...
I have been trying to use this site to run a sumproduct formula for the
below:

A B C D
E F G
projid status targetedstartdate targetedcompldate amountbilled
Onsched.


I want to count how many times the instances of a number with the
prefix
B00000 or BPR000000 occur in column A (called projid) and when "Late"
occurs
in column G (called Onsched.)

There are 4300 rows of data.

my formula is:
=SUMPRODUCT(A2:A4300="B*")*(G2:G4300="Late")

I don't get a sum of the values. Any ideas?