Subtotal formula

[pilot]

Is it possible to use a variable as a counter in a worksheet formula (no VB)? I'm sure there is a better way to do this, I just haven't been able to get there.

Assume A1:A3=ABC (in each cell). A4 and A7 are blank. A5:A6=DEF. Various other columns in this range are filled or blank. F1=2, F2=3 ,F3=5, F4 is blank, F5=4, F6=7 and F7 is blank. I want Col G to be subtotals of Col F, triggered when Col A changes. So G3(or G4)=10, G6 (or G7)=11 and other Col G cells are blank.

This form will be wiped clean of data and used again so I don't think SUBTOTALS would be good.

 

[Asala42]

G'day,

How about this formula copied down from G1:

=IF(OR(COUNTIF(A1:$A$7,A1)>1,ISBLANK(A1)),"",SUMIF($A$1:$A$7,A1,$B$1:$B$7))

Note that the countif's range has a relative reference involved: A1:$A$7

Hope this helps,
Adam

 

[pilot]

Very nice, Adam. I had not even thought of using SUMIF. It does exactly what I wanted, thank you.