SUMPRODUCT with OR?
 

Can you add more then one item in the following formula?

=SUMPRODUCT(--(Data!$F$2:$F$50000="Citibank"),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))

=SUMPRODUCT(--(Data!$F$2:$F$50000="4282" or "3826" or
"7892"),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))

SUMPRODUCT with OR?
 

You can do that by adding your criteria ranges together, and then multiplying.

=SUMPRODUCT(((Data!$F$2:$F$50000="4282")+(Data!$F$ 2:$F$50000="3826")+(Data!$F$2:$F$50000="7892"))*(D ata!$I$2:$I$50000=A9)*(Data!$H$2:$H$50000))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"pgarcia" wrote:

Can you add more then one item in the following formula?

=SUMPRODUCT(--(Data!$F$2:$F$50000="Citibank"),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))

=SUMPRODUCT(--(Data!$F$2:$F$50000="4282" or "3826" or
"7892"),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))

SUMPRODUCT with OR?
 

you can add multiple together...

=SUMPRODUCT(--(Data!$F$2:$F$50000=4282)+--(Data!$F$2:$F$50000=3826)+--(Data!$F$2:$F$50000=7892),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))

Though.. you probab;y dont need the ,--(Data!$H$2:$H$50000).

If you want to add the column H values, should be ,Data!$H$2:$H$50000 instead.

If you just want a count, leave H out entirely. Only necessary if you want
ony to count where H is not blank.

"pgarcia" wrote:

Can you add more then one item in the following formula?

=SUMPRODUCT(--(Data!$F$2:$F$50000="Citibank"),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))

=SUMPRODUCT(--(Data!$F$2:$F$50000="4282" or "3826" or
"7892"),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))

SUMPRODUCT with OR?
 

Use cells to hold the criteria:

A1 = 4282
A2 = 3826
A3 = 7892

=SUMPRODUCT(--(ISNUMBER(MATCH(Data!F2:F50000,A1:A3,0))),--(Data!I2:I50000=A9),Data!H2:H50000)

--
Biff
Microsoft Excel MVP


"pgarcia" wrote in message
...
Can you add more then one item in the following formula?

=SUMPRODUCT(--(Data!$F$2:$F$50000="Citibank"),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))

=SUMPRODUCT(--(Data!$F$2:$F$50000="4282" or "3826" or
"7892"),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))

SUMPRODUCT with OR?
 

=SUMPRODUCT((Data!$F$2:$F$50000={4282,3826,7892})* (Data!$I$2:$I$50000=A9)*(Data!$H$2:$H$50000))

--
__________________________________
HTH

Bob

"pgarcia" wrote in message
...
Can you add more then one item in the following formula?

=SUMPRODUCT(--(Data!$F$2:$F$50000="Citibank"),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))

=SUMPRODUCT(--(Data!$F$2:$F$50000="4282" or "3826" or
"7892"),--(Data!$I$2:$I$50000=A9),--(Data!$H$2:$H$50000))