SUM and COUNT Visible Cells Only | Exclude Hidden Cells | SUM or COUNT Filtered Data
HTML-код
- Опубликовано: 22 авг 2024
- Download the featured file here: www.bluepecant...
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.
------------------------
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 for this video. This issue was driving me crazy. Your presentation was perfect!
Perfect -- I needed that function #109 for my grouped data -- Thanks a million -- I'd give more than one thumbs up if I could.
Aggregate and subtotal are great functions but strangely I don’t see a lot of people using them
Great tutorial
I’ve been looking for videos on how to do this for days!!!
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.
Cool.
Very helpful. Nicely prepared example. Kudos to you Chester.
That's very useful Chester. Thank you for this new explanation. Great channel!
So helpful, so insightful. Thanks a mill!
Thank you so much
BEST CHANNEL EVER!
Thanks again,
Your #208 thumbs up viewer
Thanks for the 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
what if i dont want the vaules to chage when i unfilter a data? then what ? ( thanks in advance. the video was helpful)
I needed this thank you
It was very helpful thanks alot
Thank you. You saved me
Very helpful thanks
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? 🙏
Fandidilytastic 🙂👍 that works a treat 🍬 thanks muchfully 👏
FINALLY!! thank you so much
thank you
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
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!
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?
How can i exclude Zero value cells?
how to count filtered numbers >0. i.e. only count numbers with are positive in a filtered view.