# Thread: Conditional totals on visible cells (2007)

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

2. ## 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. ## 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?

4. ## 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.

5. ## Re: Conditional totals on visible cells (2007)

Hans,

Thank you very much. That was exactly what I needed.

#### Posting Permissions

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