Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Subtotal Formula with a Catch (Excel 2002)

    Hi!!

    I'm looking for a formula, as opposed to using a pivot table (a pivot table won't work for all the things I wish to do

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtotal Formula with a Catch (Excel 2002)

    Enter Joe in cell E2, and this array formula (confirm with Ctrl+Shift+Enter) in F2:

    =SUM(IF(($A$2:$A$31<YEAR(TODAY()))*($B$2:$B$31=E2) ,$C$2:$C$31))

    Enter the other names in E3 and E4, then fill down the formula from F2 to F4.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Subtotal Formula with a Catch (Excel 2002)

    Thanks Hans... of course that works great... I follow most of the formula but the * throws me off... to me that means multiply... can you expain the formula in a few words please so I understand the logic of it a bit more.
    Thanks again Hans!
    Lana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtotal Formula with a Catch (Excel 2002)

    The formula uses TRUE = 1 and FALSE = 0.

    The part $A$2:$A$31<YEAR(TODAY()) results in a series of TRUE/FALSE values, and the part $B$2:$B$31=E2 too. Multiplying them results in 1 if both are 1 (TRUE), otherwise in 0. So multiplying has the same effect as AND.

Posting Permissions

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