Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional totals on visible cells (2007)

    I have a table in my spreadsheet that contains cash journal entries. I would like to put a summary of totals by payment type above the table. I would also like for those totals to change whenever I autofilter on the user to only include the totals for that user. I am unable to locate a formula that will let use a conditional sum only on visible cells. Can anyone help? I have attached a sample of the spreadsheet.
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional totals on visible cells (2007)

    How about and adaptation from the method from <post:=690,691>post 690,691</post:> in F1 (confirm with ctrl-shift-enter):

    =SUM(IF(SUBTOTAL(3,OFFSET($M$7:$M$89,ROW($M$7:$M$8 9)-MIN(ROW($M$7:$M$89)),,1)),(($G$7:$G$89=E1)*($M$7:$ M$89))))

    Copy F1 to F2:F5. Copy F1 to H1:H4.

    [You must also change G1:G3 to match what you are looking up including the spaces from the list...."Cash " instead of "Cash", "Check " instead of "Check", "Visa/M" instead of of "Credit", and change F2 to "Pay De" instead of "Pay Ded" . Of course you could edit in each cell formula to explicitly look in G for what you want, but I think making the cells match the lookup is better and copy the formula]

    Steve

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional totals on visible cells (2007)

    Steve,

    That worked perfectly! Thanks so much!

    I am now having one more problem with this spreadsheet. I want to highlight cells in column M that meet the following criteria:
    The value in M is not between $4 and $6, and the value in I for the same row is Pass Daily Visit. Any suggestions?

    Thanks in advance
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Conditional totals on visible cells (2007)

    Select the cells in column M with amounts (M7:M89 in the sample workbook).
    We'll assume that M7 is the active cell within the selection.
    Select Format | Conditional Formatting...
    Select "Formula Is" from the first dropdown.
    Enter the following formula in the box next to it:
    <code>
    =AND(OR(M7<4,M7>6),I7="Pass Daily Visit")
    </code>
    Click Format...
    Activate the Pattern tab.
    Select a color.
    OK your way out.

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional totals on visible cells (2007)

    Hans,

    Thank you very much. That was exactly what I needed.
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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