Totalling many values into one summary

cr7

New Member
Hi,

I am facing a problem with totalling values from a report, I will now attempt to describe the problem clearly:

So I have 2 worksheets in this book, the summary worksheet is as below and this worksheet is the one I need to count all totals from the main worksheet, this worksheet contains ALL Product Types and Product Colour combinations (the 6 rows are purely for example and there are a lot more in the actuall summary)

 Product Type Product Colour Ordered Despatched Returned Faulty Laptop blue Desktop white Monitor black Mouse blue Laptop white

<tbody>
</tbody>

The main worksheet where the values are that I hope will populate the summary sheet:

This sheet contains 30 or so different locations of shops with the different product and product colour that they sell

The shop information is contained one after the other and seperated by 2 blank rows before the next one begins

e.g

LOCATION1
 Product Type Colour Ordered Despatched Returned Faulty Laptop blue 5 8 2 2 Laptop White white 5 15 3 3 Mouse blue 9 55 2 1

<tbody>
</tbody>

LOCATION2
 Product Type Colour Ordered Despatched Returned Faulty Laptop blue Monitor black Mouse blue

<tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>
etc... and this goes on for another 30 different locations with all the different product/colour combinations and the values against each criteria.

So what I want to do is be able to count every value for every Product/Colour combo that exists and put it into the summary sheet. So I need a formula that would look up the combo in the summary sheet then go through the entire main sheet and count all values for all 30 locations against the particular combos.

I hope I have explained this clearly, I've tried a few formulas like sum product but to no avail, the main problem being is that its not actually counting how many times a product is listed but it needs to fetch the value next to the combo under the right heading.

Another problem I face is that the data is not all aligned in the main sheet, for example location 1 may run from columns B:H and location 2 from A:G etc... so I think I need a formula that explicitly searches by values.

hope someone can help me out with this,

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Andrew Poulsom

MrExcel MVP
A couple of possibilities in C2 copied down and across:

=SUMPRODUCT(--(Sheet2!\$A\$1:\$A\$1000=\$A2),--(Sheet2!\$B\$1:\$B\$1000=\$B2),Sheet2!C\$1:C\$1000)
=SUMIFS(Sheet2!C:C,Sheet2!\$A:\$A,\$A2,Sheet2!\$B:\$B,\$B2)

The second formula requires Excel 2007 or above.

ArthriticPanda

Active Member
Hi,

I've tried to replicate what I think you might want...in a very cut down version! Is this any use?

Sheet1

 A B C D E F 1 Location 1 2 Product Type Colour Ordered Dispatched Returned Faulty 3 Laptop Black 5 8 2 1 4 Mouse Blue 5 15 3 0 5 6 7 Location 2 8 Product Type Colour Ordered Dispatched Returned Faulty 9 Laptop White 5 8 2 1 10 Mouse Pink 5 15 3 0 11 12 13 Location 3 14 Product Type Colour Ordered Dispatched Returned Faulty 15 Laptop White 5 8 2 1 16 Mouse Blue 5 15 3 0 17 18 19 20 Summary Black White Blue Pink Green 21 Laptop 1 2 0 0 0 22 Mouse 0 0 2 1 0

<colgroup><col style="font-weight:bold; width:30px; *"><col style="width:88px;"><col style="width:88px;"><col style="width:88px;"><col style="width:88px;"><col style="width:88px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula B21 {=SUM((\$A\$2:\$A\$16="Laptop")*(\$B\$2:\$B\$16="Black"))} C21 {=SUM((\$A\$2:\$A\$16="Laptop")*(\$B\$2:\$B\$16="White"))} D21 {=SUM((\$A\$2:\$A\$16="Laptop")*(\$B\$2:\$B\$16="Blue"))} E21 {=SUM((\$A\$2:\$A\$16="Laptop")*(\$B\$2:\$B\$16="Pink"))} F21 {=SUM((\$A\$2:\$A\$16="Laptop")*(\$B\$2:\$B\$16="Green"))} B22 {=SUM((\$A\$2:\$A\$16="Mouse")*(\$B\$2:\$B\$16="Black"))} C22 {=SUM((\$A\$2:\$A\$16="Mouse")*(\$B\$2:\$B\$16="White"))} D22 {=SUM((\$A\$2:\$A\$16="Mouse")*(\$B\$2:\$B\$16="Blue"))} E22 {=SUM((\$A\$2:\$A\$16="Mouse")*(\$B\$2:\$B\$16="Pink"))} F22 {=SUM((\$A\$2:\$A\$16="Mouse")*(\$B\$2:\$B\$16="Green"))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Data Validation in Spreadsheet
 Cell Allow Datas Input 1 Input 2 A3 List =\$K\$2:\$K\$5 B3 List =\$L\$2:\$L\$6 A4 List =\$K\$2:\$K\$5 B4 List =\$L\$2:\$L\$6 A9 List =\$K\$2:\$K\$5 B9 List =\$L\$2:\$L\$6 A10 List =\$K\$2:\$K\$5 B10 List =\$L\$2:\$L\$6 A15 List =\$K\$2:\$K\$5 B15 List =\$L\$2:\$L\$6 A16 List =\$K\$2:\$K\$5 B16 List =\$L\$2:\$L\$6

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

AP

cr7

New Member
Thanks so much guys, these suggestions are absolutely marvellous

Andrew - your first selection works an absolute treat, I had a feeling it would be a SUMPRODUCT so thanks for the solution

Also many thanks AP for another fine solution

thanks and regards

CR7

ArthriticPanda

Active Member
You're welcome..for completeness, to total the ordered column just add it to the end of the formula

i.e. {=SUM((\$A\$2:\$A\$16="Laptop")*(\$B\$2:\$B\$16="White"))*C3:C16}

Replies
3
Views
266
Replies
17
Views
361
Replies
2
Views
208
Replies
6
Views
258
Replies
3
Views
143

1,195,664
Messages
6,011,017
Members
441,579
Latest member
satishrazdhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back