Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Subtotal formula

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    345
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    345
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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