Using an "If" Formula for only visible cells in a list

[Shiel194]

Hello,

I have encountered a problem that I cannot seem to find an answer to in any of the forums, so I am hoping someone might have some insight to this question.

I have a large list of data with accounts that have different pieces of equipment. Several accounts have different parts, so some accounts are listed more than once. I have hidden accounts that no longer have the equipment or have no unique value.

I am trying to show the total value of the equipment for each account by showing the value on the last row that shows duplicates of the same account.

I figure I can do this by using an IF formula as follows:

=IF($A6<>$A7,"T","F")

This will come back with a T if the row is the last in its duplicate range. The problem is that the function is including data that is hidden as well, so some rows show F even though they are either different than a different account listed or not the last row in the duplicate list.

My question is: Can I use the IF function for only visible cells?

[DBY]

Hello
You can use the SUBTOTAL function, for example:

Please Login or Register  to view this content.

If you applied a filter for all the cells with "T", however, if there are hidden rows then use:

Please Login or Register  to view this content.

this will omit hidden rows.

DBY

[Shiel194]

Yes, but I am not trying to Sum the values, but rather have the cell say "T" of "F" based on whether the account in the next row is different , thus enabling me to use formulas in adjacent cells that will only show values for the accounts that show a T, meaning it is the last row in a list of duplicate accounts.

For example, if the T or F If formula is in cell B, and I used the following formula in an adjacent cell:

=IF(B:B="T",C$7-D$7,"")

This will only show the value of C-D where the column B shows a T value based on the =IF($A6<>$A7,"T","F") and the other cells will be blank

But again I run into the problem where the row should say T, but ends up with an F because the row after it is hidden...

[DBY]

Take a look at the attached example file. There a two columns of formulas. One checks 1 or zero if row is hidden and the second does the calculation depending on 1 or 0. This assumes the Account numbers are sorted and grouped together.

Perhaps this is something you can adapt to your actual sheet.

DBY