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

In this video I demonstrate how to sum and count filtered data. If you want to exclude hidden cells from your calculations you can use either the SUBTOTAL or AGGREGATE functions. Both these functions allow you to perform SUM and COUNT calculations as well as many others.

The other option is to house your data in an Excel table. Excel tables include the option to display a total row. Every cell in the total row includes a drop-down list of calculations including SUM and COUNT. These calculations use the SUBTOTAL function.
------------------------

[MichaelBriceBTS]

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!

[tdutrisac]

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

[patrickschardt]

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

Great tutorial

[jamesjennings]

Perfect -- I needed that function #109 for my grouped data -- Thanks a million -- I'd give more than one thumbs up if I could.

[WoodBlock]

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

[Hlistr]

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

[cheezycheddar]

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.

[IvanCortinas_ES]

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

[makeuplily]

BEST CHANNEL EVER!

Thanks again,
Your #208 thumbs up viewer

[bosnianinny]

So helpful, so insightful. Thanks a mill!

[florincopaci]

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

[stevecranley]

Great video thanks! I have a list with duplicate numbers in column A and I want to get a subtotal count of unique Column A numbers. How could I do that? 🙏

[shaunburchard]

Is there an equivalent (non VBA) that ignores hidden columns as opposed to rows?

[zerandervax]

Fandidilytastic 🙂👍 that works a treat 🍬 thanks muchfully 👏

[RishabhGuptaguptarishabh]

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

[m.victoriapeterson]

What if you want to count unique values of visible cells only? Can I use the UNIQUE function or will I have to resort to the division by 1?

[ERICSATIE]

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!

[djw]

What if you don't want to SUM or COUNT the values in either rows, cells or columns and all you want to know is how many unhidden or visible rows there are in any given sheet? Or is it the case that only by applying a formula can you discover this?

[nicerackz]

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?

[danysoty]

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