SUM and COUNT Visible Cells Only | Exclude Hidden Cells | SUM or COUNT Filtered Data

I needed this thank you

I’ve been looking for videos on how to do this for days!!!

Your instructions were the best in over a dozen searches. Thanks!

I wanted to count the "Distinct" Product Groups in the list of 21 items (product groups counted once for repeating line items). I came up with 10 "Distinct" Product Groups using the following formula:
{=SUM(IF(ISTEXT(C11:C31),1/COUNTIF(C11:C31, C11:C31),""))}
(The formula requires the CTRL + SHIFT + ENTER keys to be selected when done editing the formula for it to work and to add the "{" and "}" curly brackets at the start and end)

This is great to count the product groups used in the list when a much larger list is used or a report of specific groups that are exported. However, I am having trouble converting it to a "Subtotal" version that would only show visible items that are filtered or hidden. Any ideas? Would this be a substitution for the SUM and COUNTIF functions with Subtotal versions? Thanks!

Thank you so much

Thank you so much for this video. This issue was driving me crazy. Your presentation was perfect!

Thanks, I actually needed to count how many cells are not blank in an entire column. Using Subtotal(3,A:A) worked! And I used CountA(A:A) to get the total number of non-blank cells in column A and the subtotal one to get the number including the ones filtered out.

Fandidilytastic 🙂👍 that works a treat 🍬 thanks muchfully 👏

Great video, but how count the values only visibles but remove duplicates, for example on column Customer Type result will be 1 instead of 3, because Account Holder appears 3 times, so will be only one, thank you

Thank you. You saved me

FINALLY!! thank you so much

Can you please advise how to calculate total amounts when selection was completed only for the amounts which are equal or higher than (as for example) 1000. I made my filtering for column which contained cost only and i need to know the sum of those selected costs from 1000 and higher. Thank you!

Thanks for the tutorial.

thank you

is there a way to sum without filtering? let's say I wanted to see the total of "books" only without filtering and have it exclude any hidden rows?

So helpful, so insightful. Thanks a mill!

Very helpful. Nicely prepared example. Kudos to you Chester.

what if i dont want the vaules to chage when i unfilter a data? then what ? ( thanks in advance. the video was helpful)

Very helpful thanks

Hi there, I am just wondering how I can count towards the filtered table, by their "Unique product group" and by individual months. is there a formula that can cover that?

How can i exclude Zero value cells?

That's very useful Chester. Thank you for this new explanation. Great channel!

Aggregate and subtotal are great functions but strangely I don’t see a lot of people using them

Great tutorial

Thank you for this video but i have a question-how to calculate values when you have not hidden rows but hidden columns?Thank you in advance