summing for multiple criteria

[winiblues]

Hi,

I'm having trouble summing the values in the "Amount" column when there are multiple criteria.

For example: I want to sum the values in the "Amount" column when "SalesID"=215200 and the "SupplierID"=7800.

The following data is in a worksheet called "SalesData".

The total of the "Amount" to be displayed will be in another worksheet called "Sales".

I used the following formulas in the worksheet "Sales", but couldn't get it to work.
=SUMPRODUCT((SalesData!C:C=215200),(SalesData!E:E=7800),(SalesData!D:D))

OR

=SUM(IF((SalesData!C:C=215200)+(SalesData!E:E=7800),SalesData!D:D,0))

OR

{=SUM(IF((SalesData!C:C=215200)+(SalesData!E:E=7800),SalesData!D:D,0))}


"SalesData" worksheet
Column A Column B Column C
SalesID Amount SupplierID
215200 $50.00 7800
215200 $60.00 7800
215200 $90.00 7800
215200 $100.00 8800
215200 $50.00 8800
215200 $75.00 9900
225300 $150.00 9900
225300 $200.00 9900
225300 $450.00 9900
235400 $80.00 1100
235400 $90.00 1100
235400 $110.00 1100
235400 $140.00 1100

Any kind of help will be much appreciated.

Cheers,

Winiblues

 

[Pekkavee]

In SUMPRODUCT use

range C1:C1000 and not whole column C:C

Or if your cells are formatted as text:
=SUMPRODUCT((SalesData!C1:C1000="215200"),(SalesData!E1:E1000="7800"),(SalesData!D1:D1000))

Maybe ?

Pekka

 

[Chas17]

=SUM(IF(--(A1:A13=215200)*--(C1:C13=7800),B1:B13,0))

entered as an array

I changed the columns on you sorry.
Pekkavee is correct, do not use whole column.

The * is and

The + is or

Chas

 

[winiblues]

Thanks for the reponse Pekka, but that doesn't work. It gives me 0.

any other ideas, or other worksheet functions i could use, i.e. is it possible to =SUMIF with multiple criteria?

Thanks again,

Winiblues

 

[Pekkavee]

Did you check the "text format" thing which I edited into my post?

Pekka

 

[winiblues]

Hi Chas17,

I used:

{=SUM(IF(--(SalesData!C:C=215200)*--(SalesData!E:E=7800),SalesData!D:D,0))}

but it gives me a #NUM! error

any further suggestions?

thanks

Winiblues

 

[Chas17]

Are you entering it as an array formula?

You do NOT place the {} yourself,
thr formula is entered with CNTRL-SHIFT-ENTER


Chas

 

[winiblues]

Pekka adn Chas17,

I got the following to work:

{=SUM(IF(--(SalesData!C1:C1000="215200")*--(SalesData!E1:E1000="7800"),SalesData!D1:D1000,0))}

thanks for this.

But can this be done for the whole column, rather for a small range. because sometime i have 30000 rows, other times there may 50000 rows?

cheers

Winiblues

 

[Chas17]

Why
{=SUM(IF(--(SalesData!C:C=215200)*--(SalesData!E:E=7800),SalesData!D:D,0))}

and not
{=SUM(IF(--(SalesData!A1:A100=215200)*--(SalesData!C1:c100=7800),SalesData!b1:b100,0))}

Col A B c
215200 $50.00 7800

You can NOOT use c:c

use C1:c100

 

[Chas17]

You can NOT use the whole col

use

{=SUM(IF(--(SalesData!C1:C50000="215200")*--(SalesData!E1:E50000="7800"),SalesData!D1:D50000,0))}