Summing up data with an array formula
 

I have a table of data arranged as below:

Parent Name Prod Type April Sales
Company A Wood 2,833
Company A Wood 2,150
Company A Wood 4,872
Company B Other 75
Company B Steel 1,100
Company C Steel 50
Company C Other 785

I would like to summarize it in a table like this:

Wood Other Steel
Company A
Company B
Company C

I would like to use an array formula, the "parent name" and "product type"
columns would be named ranges and constant. I have not been able to get this
to work. I have also tried using the sumif function.

I don't want to do this with Pivot table.

Thanks
K

Thanks

Use the following formula in cell B20
=SUMPRODUCT(--($A$2:$A$9=$A20),--($B$2:$B$9=B$19),$C$2:$C$9)
This assumes that your result grid starts in A19 that is in A20 you have
Company A and in Cell B19 you have Wood and so on.
Copy the formula to the right and down as required.

Alok Joshi

"Kofi" wrote:

I have a table of data arranged as below:

Parent Name Prod Type April Sales
Company A Wood 2,833
Company A Wood 2,150
Company A Wood 4,872
Company B Other 75
Company B Steel 1,100
Company C Steel 50
Company C Other 785

I would like to summarize it in a table like this:

Wood Other Steel
Company A
Company B
Company C

I would like to use an array formula, the "parent name" and "product type"
columns would be named ranges and constant. I have not been able to get this
to work. I have also tried using the sumif function.

I don't want to do this with Pivot table.

Thanks
K

Thanks

Thanks it worked like a charm.

What does the "--" in the formula do?

Thanks

"Alok" wrote:

Use the following formula in cell B20
=SUMPRODUCT(--($A$2:$A$9=$A20),--($B$2:$B$9=B$19),$C$2:$C$9)
This assumes that your result grid starts in A19 that is in A20 you have
Company A and in Cell B19 you have Wood and so on.
Copy the formula to the right and down as required.

Alok Joshi

"Kofi" wrote:

I have a table of data arranged as below:

Parent Name Prod Type April Sales
Company A Wood 2,833
Company A Wood 2,150
Company A Wood 4,872
Company B Other 75
Company B Steel 1,100
Company C Steel 50
Company C Other 785

I would like to summarize it in a table like this:

Wood Other Steel
Company A
Company B
Company C

I would like to use an array formula, the "parent name" and "product type"
columns would be named ranges and constant. I have not been able to get this
to work. I have also tried using the sumif function.

I don't want to do this with Pivot table.

Thanks
K

Thanks

Kofi,

-- converts the false values to 0 and true to 1

Alok

"Kofi" wrote:

Thanks it worked like a charm.

What does the "--" in the formula do?

Thanks

"Alok" wrote:

Use the following formula in cell B20
=SUMPRODUCT(--($A$2:$A$9=$A20),--($B$2:$B$9=B$19),$C$2:$C$9)
This assumes that your result grid starts in A19 that is in A20 you have
Company A and in Cell B19 you have Wood and so on.
Copy the formula to the right and down as required.

Alok Joshi

"Kofi" wrote:

I have a table of data arranged as below:

Parent Name Prod Type April Sales
Company A Wood 2,833
Company A Wood 2,150
Company A Wood 4,872
Company B Other 75
Company B Steel 1,100
Company C Steel 50
Company C Other 785

I would like to summarize it in a table like this:

Wood Other Steel
Company A
Company B
Company C

I would like to use an array formula, the "parent name" and "product type"
columns would be named ranges and constant. I have not been able to get this
to work. I have also tried using the sumif function.

I don't want to do this with Pivot table.

Thanks
K

Thanks