Formula required for an awkward SUMIF variation

[Jabba1963]

Hi,


Getting all confused with MATCH, INDEX, SUMIFS, COUNTIFS, etc...


A B C

1 Ref Sub-Ref Total

2 P001 00 16 <---- Looking to calculate this figure

3 P001 01 2

4 P001 02 4

5 P001 03 2 …based on the sum of these figures

6 P001 04 1 /

7 P001 05 5 /

8 P001 06 2 /

9 P002 00 22 <---- Looking to calculate this figure

10 P002 01 8

11 P002 02 5 …based on the sum of these figures

12 P002 03 9 /

13 P003 00 18 <---- Looking to calculate this figure

14 P003 01 3

15 P003 02 6 …based on the sum of these figures

16 P003 03 4 /

17 P003 04 5 /


The awkward bit is that the number of line entries varies per REF value... and trying desperately to avoid the OFFSET function due to performance issues and OFFSET being a volatile function...


Any help gratefully received...


Many thanks in advance

Jabba

 

[NARAYANK991]

Hi Jabba ,


I am not sure I have understood you correctly , but if your data is as follows , from A1:C17 :

[pre]

Ref   Sub-Ref   Total
P001	0
P001	1	2
P001	2	4
P001	3	2
P001	4	1
P001	5	5
P001	6	2
P002	0
P002	1	8
P002	2	5
P002	3	9
P003	0
P003	1	3
P003	2	6
P003	3	4
P003	4	5

[/pre]
then in D2 , put in the following formula , and copy down :


=IF(B2=0,SUMIF($A$2:$A$17,A2,$C$2:$C$17),"")


Narayan

 

[Jabba1963]

Hi Narayan


And thanks for getting back to me... and yes you have understood perfectly and yes you are right of course this works but only if I am putting the formula in a new column D.


The problem is that strangely I need the result in column C above the elements it is summing... so I need the results in C2, C9 and C13 respectively and I am trying to avoid circular references... which so far I have not been able to


Regards

Jez

 

[NARAYANK991]

Hi Jez ,


If you need the formulae in C2 , C9 , C17 and beyond , are you going to put in the formula in C2 , and then copy it to the required cells individually ?


If so , try this :


=SUM(C3:INDEX(C3:$C$17,IF(ISNA(MATCH(TRUE,B3:$B$17=0,0)-1),ROWS(C3:$C$17),MATCH(TRUE,B3:$B$17=0,0)-1)))


entered as an array formula , using CTRL SHIFT ENTER ; you will have to change the absolute references $B$17 and $C$17 to whatever are the last cells of your data range.


Narayan

 

[Jabba1963]

Hi Narayan


Thanks again but I was struggling to get the CSE working so started from scratch and worked my through it step by step and finally came up with this that gives me the result I need... by putting this into the C2, C9 and C17 etc...


=SUM(INDIRECT(CHAR(64+COLUMN())&(ROW()+1)&":"&CHAR(64+COLUMN())&COUNTIF($A:$A,$A2)-1+ROW()))


Many thanks for your assistance and much respect for anyone who has a grasp of CSE like you Sir - thanks again