SumIf: dynamically change based on autofilter

[jayexcel1]

Hi,

I have data as attached.
I have the sumif function which works fine; but when I filter the data by selecting a city, the sumif value does not change accordingly.

Please help.

See attached data.
jay

 

[Luke M]

Check out this thread, which shows several ways to combine SUMIF with SUBTOTAL (the latter is needed when dealing with filtered rows).
http://chandoo.org/forum/threads/combining-subtotal-and-sumif.14209

 

[NARAYANK991]

Hi Jay ,

See if this works ; it is based on a recent answer to a similar question here :

http://chandoo.org/forum/threads/co...here-criteria-is-met-in-column-a-cells.22576/

Narayan

 

[Hui]

Have a read of this post which offers an alternative solution:
http://chandoo.org/wp/2014/07/23/formula-forensics-no-037-how-to-count-and-sum-filtered-tables/

 

[jayexcel1]

Thanks a lot Luke, Narayan and Hui. Learnt something new.