Thanks:  0
Likes:  0

# Thread: Subtotal formula

1. 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.

2. G'day,

=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,

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•