Totalling many values into one summary

[cr7]

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,

thanks in advance

 

[Andrew Poulsom]

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]

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>

Spreadsheet Formulas

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]

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]

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}