The most straight forward explanation of how to solve this problem. Amazing video. I spent an entire day trying to figure out a solution. So happy I found this tutorial. The best seven minutes I have ever spent on Excel learning.
I had same experience, got stuck on a problem and I literally spent over 12hrs trying to solve. Thanks to "Smart Office" for this short but detailed video.👍
This is extremely helpful. Could you please explain how to count blank spaces while filtering data? In other words how to count blank spaces with specific criteria from filters. I am using the COUNTBLANK formula but it counts all the blank spaces from the column and I would like to try to get numbers after filtering information. Similar to what is being done in this video, but counting blank spaces instead of words.
Is it possible to do this if you you have multiple criteria? For example, if your original formula was =COUNTIFS(C6:C20, "RPA", D6:D20,"Present") and you wnat to filter for Manager A.
What if I am trying to count the number of cells greater than zero? my table has "hours late" where your "attendance" column is. I am trying to count the number of cells greater than 0 when data is filtered.
In my case it's not text it a number; I want to know how many 90 I have after filtering it just shows me 0. Here's what I used: =SUMPRODUCT(SUBTOTAL(3,OFFSET(O8:O400,ROW(O8:O400)-MIN(ROW(O8:O400)),,1,)),N(O8:O400="90"))
How do I do this with a conditional where the condition is that a value in the column must be greater than 0? Also I am not sure if it is messing up for me because I am using table format for my column data such as "Table[Cost]"
I am getting the error "SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count 1. Actual row count: 107. column count: 1." Please help me out with this
Is there a way to count if the cell contains "present"? I have this working but I'm trying on a column that has the word "yes" and then a date (ex: "yes 1/24/24"). I try to just use wildcards in the formula as show above but it's only counting the cells that are "yes" and not "yes 1/24/24". Here is my formula... =SUMPRODUCT(SUBTOTAL(3, OFFSET(X2:X126,ROW(X2:X126)-MIN(ROW(X2:X126)),,1,)),N(X2:X126="*Yes*")) Notice my attempt to use * wildcards around Yes
Did you have a reply for this error as I am getting the same in Google Sheets. It would be great if you can make this work in Google sheets and demonstrate this as there are a ton of people asking for this solution online and do not seem to be able to make this work in sheets. Thx@@be-smart-at-office
I am getting the error "SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count 1. Actual row count: 1276. column count: 1." This is the formula I typed in: =SUMPRODUCT(SUBTOTAL(3,OFFSET(V8:V2000,ROW(V8:V2000)-MIN(ROW(V8:V2000)),,1,)),N(V8:V2000="*CASH*")) I want it to count every visible instance of the word CASH in the V Column. There are multiple variations of CASH (i.e. CASH 1, CASH 2, etc).
The most straight forward explanation of how to solve this problem. Amazing video. I spent an entire day trying to figure out a solution. So happy I found this tutorial. The best seven minutes I have ever spent on Excel learning.
Thank you 🙏
I had same experience, got stuck on a problem and I literally spent over 12hrs trying to solve.
Thanks to "Smart Office" for this short but detailed video.👍
Thank you man, I had done this formula before but didn't remember, you presented it the easiest way possible.
Amazing video, thanks mate! No one else covers this stuff, golden material.
Welcome 🙏
Thank you! This is so well explained and has saved me mass amounts of time!
Welcome 🙏
Fantastic video, short and to the point
Truly amazing how often sb with an indian(?) accent has saved my day with a short youtube clip
Thank you 🙏
How can I like this video several times?????? Thank you so much!!
Welcome 🙏
This is extremely helpful. Could you please explain how to count blank spaces while filtering data? In other words how to count blank spaces with specific criteria from filters. I am using the COUNTBLANK formula but it counts all the blank spaces from the column and I would like to try to get numbers after filtering information. Similar to what is being done in this video, but counting blank spaces instead of words.
Its not often i go to RUclips and get exactly what i want when trying to resolve an excel issue. This one is excellent, well done and thanks.
Thank you 🙏
Is it possible to sum a total value as opposed to count@@be-smart-at-office
Hi there, don't worry, I've solved it
I respect you on behalf of all of koreans...감사합니다
Thank you 🙏
Bravo! Thank you soo much!
Welcome 🙏
good video mate , this solved 3 hrs wasted at list , thank you
Thank you 🙏
Thank you very much. It's very much helped for my report.
Really helpful. Thank you
Welcome 🙏
not working in google sheets
sumproduct mismatch. Anyone Help
Is it possible to do this if you you have multiple criteria? For example, if your original formula was =COUNTIFS(C6:C20, "RPA", D6:D20,"Present") and you wnat to filter for Manager A.
Thank you, this was extremely helpful.
Thank you so much 🙏
Wow thank you, it works! Don't ask me to explain the formula though.
Great 👍
Thank you so much
You're most welcome
Thank you very much for the helpful video.
Welcome 🙏
Thanks for this my friend.
Very welcome
good stuff, thanks
Welcome 🙏
Very helpful
Thank you 🙏
Thank you very much it works well
Glad to hear that
Superb, thank u so much ❤
What if I am trying to count the number of cells greater than zero? my table has "hours late" where your "attendance" column is. I am trying to count the number of cells greater than 0 when data is filtered.
Thanks, much appreciated
Welcome 🙏
Can we use same formula on google sheet?
Great vid! Thanks. How can I add the condition of =">today()" ?
In my case it's not text it a number; I want to know how many 90 I have after filtering it just shows me 0.
Here's what I used:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(O8:O400,ROW(O8:O400)-MIN(ROW(O8:O400)),,1,)),N(O8:O400="90"))
In case of numbers, it should not be enclosed with double quotation "90", only keep 90
Very nice 👍👍👍
Thanks a lot
How do I do this with a conditional where the condition is that a value in the column must be greater than 0? Also I am not sure if it is messing up for me because I am using table format for my column data such as "Table[Cost]"
In the place of N(D6:D20="Present")
Change it to N(D6:D20 >0)
I will try that. I had been using ">0" so I will try without the quotation marks@@be-smart-at-office
Hi, how can I count the unique value in the B column subject to the D column Value?
I am getting the error
"SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count 1.
Actual row count: 107. column count: 1."
Please help me out with this
Thank you so much!
Welcome 🙏
How about the value more than one ?
Thank you!
Welcome
Is there a way to count if the cell contains "present"? I have this working but I'm trying on a column that has the word "yes" and then a date (ex: "yes 1/24/24"). I try to just use wildcards in the formula as show above but it's only counting the cells that are "yes" and not "yes 1/24/24". Here is my formula...
=SUMPRODUCT(SUBTOTAL(3, OFFSET(X2:X126,ROW(X2:X126)-MIN(ROW(X2:X126)),,1,)),N(X2:X126="*Yes*"))
Notice my attempt to use * wildcards around Yes
How do I use countifs function in filtered data?
Very nice, but it not working in google sheet please help me
with countif i want to know todays absent and present
This formula worked to count "text" but not the "digit" like 1, 2, 3, how to count digits please?
For digits you just need subtotal
Facing the same problem...i have given remarks like no 1 for present and 2 for absent then how we count it...
Quite complicated
i get this error: SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 196, column count: 1.
Could you plz share the file, spreadsheetforu@gmail.com
i will check for the error
Did you have a reply for this error as I am getting the same in Google Sheets. It would be great if you can make this work in Google sheets and demonstrate this as there are a ton of people asking for this solution online and do not seem to be able to make this work in sheets. Thx@@be-smart-at-office
Was there a solution for this?
I am getting the error "SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count 1. Actual row count: 1276. column count: 1." This is the formula I typed in:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(V8:V2000,ROW(V8:V2000)-MIN(ROW(V8:V2000)),,1,)),N(V8:V2000="*CASH*"))
I want it to count every visible instance of the word CASH in the V Column. There are multiple variations of CASH (i.e. CASH 1, CASH 2, etc).
Hi, could you please share your file to spreadsheetforu@gmail.com
I will check for the issue and revert
I got 0 count
Please share your working sheet (if possible) to spreadsheetforu@gmail.com, will check for the issue and revert