summing for multiple criteria

winiblues

New Member
Joined
Oct 8, 2004
Messages
11
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
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 ? :confused: :confused:

Pekka
:eek: :eek:
 

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
=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

New Member
Joined
Oct 8, 2004
Messages
11
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

Well-known Member
Joined
May 25, 2004
Messages
1,218

ADVERTISEMENT

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

Pekka
:eek: :eek:
 

winiblues

New Member
Joined
Oct 8, 2004
Messages
11
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

Well-known Member
Joined
Oct 16, 2002
Messages
657

ADVERTISEMENT

Are you entering it as an array formula?

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


Chas
 

winiblues

New Member
Joined
Oct 8, 2004
Messages
11
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

Well-known Member
Joined
Oct 16, 2002
Messages
657
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

Well-known Member
Joined
Oct 16, 2002
Messages
657
You can NOT use the whole col

use

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

Watch MrExcel Video

Forum statistics

Threads
1,118,386
Messages
5,571,829
Members
412,421
Latest member
grace_abar
Top