Need formula

[Sandy]

Hello!

I need to get the sum of the same region categories for
the same item amount divided by count of same region.
Below is some sample data.

Any suggestions?

Sandy

ID categ region item amount

1 115 1 A 423

2 115 1 B 201

3 115 1 C -22096

4 115 1 D 13

5 236 1 A 576

6 236 1 B 210

7 236 1 C -32916

8 236 1 D 20

9 223 2 A 949

10 223 2 B 404

11 223 2 C -39703

12 223 2 D 76

13 135 3 A 475

14 135 3 B 430

15 135 3 C -28512

16 135 3 D 50

17 137 3 A 860

18 137 3 B 507

19 137 3 C -26033

20 137 3 D 399

21 145 3 A 1100

22 145 3 B 1083

23 145 3 C -50109

24 145 3 D 99

25 420 4 A 838

26 420 4 B 798

27 420 4 C -37503

28 420 4 D 17

29 348 4 A 629

30 348 4 B 497

31 348 4 C -55954

32 348 4 D 27

33 14 5 A 1185

34 14 5 B 1016

35 14 5 C -58305

36 14 5 D 366

37 36 5 A 2221

38 36 5 B 1493

39 36 5 C -226006

40 36 5 D 255

 

[Chip Pearson]

Sandy

You can use a Pivot Table to do this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Lance]

col
H I J K L
A B C D
1 124.875 51.375 -6876.5 4.125
2 237.25 101 -9925.75 19
3 202.916 168.33 -8721.16 45.66
4 183.375 161.875 -11682.125 5.5
5 425.75 313.625 -35538.875 77.625

=SUMPRODUCT(($C$2:$C$41=$H2)*($D$2:$D$41=I$1)*
($E$2:$E$41))/COUNTIF($C$2:$C$41,$H2)

with your data in a1:e41

Lance