SumProduct or sumif
 

I need a subtotal for 5 different products in a table
Column A will have 5 different colors (Blue, Green, Red, Black)

Column L has a price/rate
Column O - R have Quantity for each week (total of 4)

I need to have subtotals for each color for each week.

A L O P Q R
Blue 290 5 5 5 0
Green 190 5 5 5 5
Red 0 6 6 6 6
Green 10 10 10 10 10
Green 190 0 0 0 8
Black 20 5 5 5 5

From the example above I need subtotals for each color by week.

Any way to get this done with Sumproduct or sumif?

Greg

SumProduct or sumif
 

=SUMPRODUCT((A2:A7="Black")*(B2:B7)*(C2:F7))

--
__________________________________
HTH

Bob

"TitanG" wrote in message
...
I need a subtotal for 5 different products in a table
Column A will have 5 different colors (Blue, Green, Red, Black)

Column L has a price/rate
Column O - R have Quantity for each week (total of 4)

I need to have subtotals for each color for each week.

A L O P Q R
Blue 290 5 5 5 0
Green 190 5 5 5 5
Red 0 6 6 6 6
Green 10 10 10 10 10
Green 190 0 0 0 8
Black 20 5 5 5 5

From the example above I need subtotals for each color by week.

Any way to get this done with Sumproduct or sumif?

Greg

SumProduct or sumif
 

Hi

There are only 4 colours listed.
No matter
in S2 Enter Red, S3 Green etc.
In T1 enter Wk1, U1 Wk2 etc.
In T2 enter
=SUMIF(A:A,$S2,O:O)
Copy Across through cells U2:W2
Copy T2:W2 down through T3:T5

--
Regards
Roger Govier

"TitanG" wrote in message
...
I need a subtotal for 5 different products in a table
Column A will have 5 different colors (Blue, Green, Red, Black)

Column L has a price/rate
Column O - R have Quantity for each week (total of 4)

I need to have subtotals for each color for each week.

A L O P Q R
Blue 290 5 5 5 0
Green 190 5 5 5 5
Red 0 6 6 6 6
Green 10 10 10 10 10
Green 190 0 0 0 8
Black 20 5 5 5 5

From the example above I need subtotals for each color by week.

Any way to get this done with Sumproduct or sumif?

Greg

SumProduct or sumif
 

This is spot on! Thanks.

"Bob Phillips" wrote:

=SUMPRODUCT((A2:A7="Black")*(B2:B7)*(C2:F7))

--
__________________________________
HTH

Bob

"TitanG" wrote in message
...
I need a subtotal for 5 different products in a table
Column A will have 5 different colors (Blue, Green, Red, Black)

Column L has a price/rate
Column O - R have Quantity for each week (total of 4)

I need to have subtotals for each color for each week.

A L O P Q R
Blue 290 5 5 5 0
Green 190 5 5 5 5
Red 0 6 6 6 6
Green 10 10 10 10 10
Green 190 0 0 0 8
Black 20 5 5 5 5

From the example above I need subtotals for each color by week.

Any way to get this done with Sumproduct or sumif?

Greg

SumProduct or sumif
 

This worked once I adjusted
=SUMIF($A:$A,$S2,O:O)

Is there a way to automatically fill in the color in col S

In other words if column A has only four colors then S would only show those
colors that are showing in A.

"Roger Govier" wrote:

Hi

There are only 4 colours listed.
No matter
in S2 Enter Red, S3 Green etc.
In T1 enter Wk1, U1 Wk2 etc.
In T2 enter
=SUMIF(A:A,$S2,O:O)
Copy Across through cells U2:W2
Copy T2:W2 down through T3:T5

--
Regards
Roger Govier

"TitanG" wrote in message
...
I need a subtotal for 5 different products in a table
Column A will have 5 different colors (Blue, Green, Red, Black)

Column L has a price/rate
Column O - R have Quantity for each week (total of 4)

I need to have subtotals for each color for each week.

A L O P Q R
Blue 290 5 5 5 0
Green 190 5 5 5 5
Red 0 6 6 6 6
Green 10 10 10 10 10
Green 190 0 0 0 8
Black 20 5 5 5 5

From the example above I need subtotals for each color by week.

Any way to get this done with Sumproduct or sumif?

Greg