sumproduct for multiple criteria in different columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gbpg - 31 Mar 2008 03:01 GMT

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?

Reply to this Message

T. Valko - 31 Mar 2008 03:24 GMT

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

Signature

Biff
Microsoft Excel MVP

>I have been trying to use this site to run a sumproduct formula for the
>below:
[quoted text clipped - 15 lines]
>
> I don't get a sum of the values. Any ideas?

Reply to this Message

Rick Rothstein (MVP - VB) - 31 Mar 2008 03:27 GMT

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

>I have been trying to use this site to run a sumproduct formula for the
>below:
[quoted text clipped - 15 lines]
>
> I don't get a sum of the values. Any ideas?

Reply to this Message

gbpg - 31 Mar 2008 04:05 GMT

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?

> Try it this way...
>
[quoted text clipped - 27 lines]
> >
> > I don't get a sum of the values. Any ideas?

Reply to this Message

T. Valko - 31 Mar 2008 04:18 GMT

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

Signature

Biff
Microsoft Excel MVP

> they both returned the same value of 214 (after I changed Ricks 43 to
> 4309).
[quoted text clipped - 34 lines]
>> >
>> > I don't get a sum of the values. Any ideas?

Reply to this Message