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