Subtotal formula

pilot · Apr 23rd, 2002, 02:19 PM

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 · Apr 23rd, 2002, 04:06 PM

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 · Apr 23rd, 2002, 08:12 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 23rd, 2002, 02:19 PM	#1
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	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.

Apr 23rd, 2002, 04:06 PM	#2
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,029	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

Apr 23rd, 2002, 08:12 PM	#3
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	Very nice, Adam. I had not even thought of using SUMIF. It does exactly what I wanted, thank you.