sumproduct for multiple criteria in different columns

[gbpg]

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?

 

[T. Valko]

*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.

 

[Rick Rothstein \(MVP - VB\)]

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]

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?

 

[T. Valko]

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))