match formula only on visible rows

[Alina Loredana]

Hi all,

i'm trying to write a formula that searches a value in a range, the problem being that the range may or may not contain hidden rows. I need it to sum up values only on visible rows and only if it does not find a specific value in a range, also only on visible rows. If it does find the specific value, i need it to return "salary on 5000"

see sample file attached. depending on value selected in I2, rows are shown/hidden. I need the formula in I14 to work only on visible rows
I used subtotal, but I need match to work similar as well.

=IF(ISERROR(MATCH(5000,F3:F7,0)),SUBTOTAL(109,F3:F7),"salary of 5000")

Thank you in advance :D

[Kaper]

Have a look into recent thread: http://www.excelforum.com/excel-form...ml#post4131780 you can adopt similar method.

[bebo021999]

Try:

Please Login or Register  to view this content.

[Alina Loredana]

Thank you.

But now I realised I need it to do somenthing else, explanation bellow:

If somewhere in the range F3:F7 I have "no salary" and only if the row is visible, then in I14 i need to have "no salary".
If I have values, then I need the sum of those values that are on visible rows.

how should I change your formula:

Please Login or Register  to view this content.

It's quite complicated to explain, hopefully I made myself understood

Thanks.

[Alina Loredana]

and the modified sample attached

[Kaper]

May be?

Please Login or Register  to view this content.

(edit: answered before you posted the attachment - will check in a minute)

[Kaper]

yes it seems to work how I understood:
- if in visible is "no sallary" then result is no sallary
__- if not then if in visible is 5000 then result is sallary of 5000
____- otherwise it is sum of visible sallaries

[Alina Loredana]

actually, I didn't need the part with "salary of 5000" anymore.

I managed to modify it and it works great! Thank you so much! You saved me :D

Please Login or Register  to view this content.

not sure I fully understand the offset part, can u walk me through it?

Thank you again.

[Kaper]

The offset is used here to create a table of subtotal results each for single row. See how it corresponds with helper column in the linked above thread.

[Alina Loredana]

ok, thank you again.