how to use sumif with filtering

[jazztech]

Dear All,

I have a sumif function in a cell which adds values from a table from another sheet. When I apply a filter in the table, the sumif still includes the invisible rows. Is there a simple way to add only the visible rows with a function based on a criteria like with the sumif. i.e. after certain rows are not visible, still a further slection has to be made base don another cell in the same row.

Many thanks in advance.

Jazztech

 

[venkat1926]

use
specialcells(xlcelltypevisible)

 

[mfexcel]

You may consider SUBTOTAL
By applying different filters, it essentially means SUMIF(s)...
Take a look at this and see if it helps:
SUM vs. SUBTOTAL | wmfexcel

Dear All,

I have a sumif function in a cell which adds values from a table from another sheet. When I apply a filter in the table, the sumif still includes the invisible rows. Is there a simple way to add only the visible rows with a function based on a criteria like with the sumif. i.e. after certain rows are not visible, still a further slection has to be made base don another cell in the same row.

Many thanks in advance.

Jazztech

 

[draagreajk]

Hi
Im using =SUMIF(K42:K60,"Mark F",H42:H60) or =SUMPRODUCT(--(K42:K60="Mark F"),H42:H60) to work how many hours Mark F works in a day per duration the K42:K60 is the name column and the H42:H60 is the duration. I need to know what formula i need to be able to use with a filter. Currently when filtering the total stays the same regardless of what i do. Please help