Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula presumes cells have data | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Conditional summing with large amounts of data | Excel Worksheet Functions | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel | |||
matching data formula? | Excel Worksheet Functions |