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

 

[winiblues]

thanks for your promt respones guys.
i've decided to use:

{=SUM(IF(--(SalesData!C1:C65535="215200")*--(SalesData!E1:E65535="7800"),SalesData!D1:D65535,0))}

which is one less than the total number of rows. it seems to work fine.

thanks again,

Winiblues

 

[winiblues]

one more question guys, what does the

"--" do in the formula:

=SUM(IF(--(SalesData!C1:C65535="215200")*--(SalesData!E1:E65535="7800"),SalesData!D1:D65535,0))

thanks

winiblues

 

[Pekkavee]

"--" turns "TRUE" or "FALSE" into a numeric value 1 or 0 so you can multiply with it in the formula.

Pekka

 

[winiblues]

thanks for all your help Pekka.

cheers,

winiblues

 

[Chas17]

You're Welcome

 

[Aladin Akyurek]

thanks for your promt respones guys.
i've decided to use:

{=SUM(IF(--(SalesData!C1:C65535="215200")*--(SalesData!E1:E65535="7800"),SalesData!D1:D65535,0))}

which is one less than the total number of rows. it seems to work fine.

thanks again,

Winiblues

If this formula achieves the intended calculation, it needs some cleaning up:

=SUM(IF((SalesData!C1:C65535="215200")*(SalesData!E1:E65535="7800"),SalesData!D1:D65535))

which you need to confirm with control+shift+enter.

Equivalently:

=SUMPRODUCT(--(SalesData!C1:C65535="215200"),--(SalesData!E1:E65535="7800"),SalesData!D1:D65535)

In fact, you could create an additional column, say F, where you can concatenate column C with column E on SalesData like this:

F1, copied down:

=C1&"#"&E1

Then invoke:

=SUMIF(SalesData!F:F,X2&"#"&Y2,SalesData!D:D)

where X2 houses a condition like 215200 and Y2 a condition like 7800.

 

[Chas17]

Aladin,
Good point on cleanup, the sumIF doesn't need the -- for 1/0.

When you say C1&#&E1

What is the # symbol doing?

=SUMIF(SalesData!F:F,X2&"#"&Y2,SalesData!D:D)

Thanks,
Chas

 

[Aladin Akyurek]

Aladin,
Good point on cleanup, the sumIF doesn't need the -- for 1/0.

When you say C1&#&E1

What is the # symbol doing?

=SUMIF(SalesData!F:F,X2&"#"&Y2,SalesData!D:D)

Thanks,
Chas

Concatenating must avoid traps like:

1,11
11,1

so that it doesn't produce:

111
111

# prevents such an outcome:

1#11
11#1

 

[Chas17]

Thank you Aladin.

Simple solutions sometimes elude when you're looking too hard.
That's just too easy.

Thanks,
Chas