How do I sum cells by refering to discrete values?

[Guest]

I have a small table which is 12 rows deep.

It can be simplified and summarised as follows;

A B C D
1 10 1 2
2 5 1 3
3 16 2 2
4 1 3 2
5 13 4 3
6 16 4 2
7 17 1 3
8 18 3 3
9 12 5 3
10 12 6 2
11 14 7 2
12 16 2 2

Colum A can contain any value, Colum B is restricted to integers 1-8, and
Colum C is restricted to either 2 or 3.

I require Colum D to show the sum of ("A#"/"C#") for each row which has the
same number in Colum B and then list it against the first row with that
number only

thus the completed table would be as follows

A B C D
1 10 1 2 12.3
2 5 1 3
3 16 2 2 16.0
4 1 3 2 6.5
5 13 4 3 12.3
6 16 4 2
7 17 1 3
8 18 3 3
9 12 5 3 4
10 12 6 2 6
11 14 7 2 7
12 16 2 2

I’ve searched and found partial solutions but cant seam to work out a string
to go in the Colum D cells that will do it all.

Thanks in advance.

 

[Guest]

Hi,

I'm confused (not unusual for me at my age) but I can't wotk out how the
numbers in column D are arrived at. I think explaining what "sum of
("A#"/"C#")" would be helpful.

Mike

 

[Bob Phillips]

=IF(COUNTIF($B$1:B1,B1)=1,SUMPRODUCT(--($B$1:$B$12=B1),$A$1:$A$12/$C$1:$C$12),"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Max]

Another thought ..

Put in D1:
=IF(COUNTIF(B$1:B1,B1)>1,"",SUMIF(B:B,B1,A:A)/SUMIF(B:B,B1,C:C))
Copy down. This yields:

10 1 2 4
5 1 3
16 2 2 8
1 3 2 3.8
13 4 3 5.8
16 4 2
17 1 3
18 3 3
12 5 3 4
12 6 2 6
14 7 2 7
16 2 2

 

[Guest]

This is an array formula, committed by pressing Ctrl-Shift-Enter

=IF(COUNTIF(B$1:B1,B1)=1,SUMPRODUCT(--($B$1:$B$12=B1),$A$1:$A$12/$C$1:$C$12),"")

 

[Bob Phillips]

This is an array formula, committed by pressing Ctrl-Shift-Enter

=IF(COUNTIF(B$1:B1,B1)=1,SUMPRODUCT(--($B$1:$B$12=B1),$A$1:$A$12/$C$1:$C$12),"")

No it isn't.

 

[Guest]

Mea culpa

No it isn't.

 

[Guest]

Wasnt exacxtly what i needed but it provided me the method i required