Sum of visible prices in column only

[jwdemo]

I have a sheet showing many different items from many different grocery departments and cost variances associated with each item. I want a total at the top that will add togeteher all of the visible prices in the cost variance column. When I use =SUM, it is adding everything in the column whether it is filtered out or not.

Any advice on what formula to use that would only sum visible prices?

Thanks so much for the help!

Jess

 

[Eric W]

Try:

=SUBTOTAL(109,I:I)

 

[jwdemo]

Try:

=SUBTOTAL(109,I:I)

What does the "109" indicate?

 

[Eric W]

SUBTOTAL is actually a composite function, it does a lot of things. COUNT, COUNTA, MAX, MIN, PRODUCT, SUM, etc. The main reason it exists is to provide the ability to ignore hidden rows. So the first value, 109 here, means SUM. A 9 just means SUM, but ignore any rows that have been filtered. If you add 100 to it, it ignores any filtered rows, plus any rows you manually hide. To see what the other options there are, you can look at the Intellisense when you type in the formula, or Google the SUBTOTAL function.

 

[jwdemo]

SUBTOTAL is actually a composite function, it does a lot of things. COUNT, COUNTA, MAX, MIN, PRODUCT, SUM, etc. The main reason it exists is to provide the ability to ignore hidden rows. So the first value, 109 here, means SUM. A 9 just means SUM, but ignore any rows that have been filtered. If you add 100 to it, it ignores any filtered rows, plus any rows you manually hide. To see what the other options there are, you can look at the Intellisense when you type in the formula, or Google the SUBTOTAL function.

Awesome, thanks! I really appreciate the help and the extra info.