Count values excluding formulas
Hi to all!
I am trying to find a formula to count only cells that have a value but ignoring if blank, even though all the cells have a formula.
Ex: Column A has 10 rows with 5 returned values. I want a formula that will give me a count of 5 only. I don't want a sum of those values, just a count of how many values there actually are.
Any help is appreciated!
Data Range
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
How about:
Formula:
Please Login or Register to view this content.
or:
Formula:Please Login or Register to view this content.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
alansidman =IF(B1>0,C1,"") is not needed as I would only be going down the columns, not across. =COUNT(A1:A10) does not work as it still counts every blank cell due to there being a formula.
kev_, neither of those work...It just returns a blank cell.
Thanks for trying!
Last edited by UGAmom; 03-02-2018 at 02:18 PM.
They work for me - so our assumptions must be different.
Post a workbook so that we are looking at the same thing
- click on Go Advanced \ look below and click on ManageAttachments \ follow screen instructions
Thanks ��
normal COUNT should work ?_?
assuming all the return values are in number
=count(A1:A10)
Maybe I'm not entering in formula right...? Most of my experience with excel has been learned through google.
See attachment:
In column AM I need to return value to be 3, not 3/4 and not 5. I'll be putting the formula in cell AM6
finalazy, normal count does not work because all of the cells have a formula so even though the return value in the cell is blank it is being counted and I need it not to be.
All those formulas return 0 (not null strings) if there is no occurrence by type, which is why COUNT() counts them, but you have options set to suppress display of zeros on that worksheet. Use instead ...
=COUNTIF(AK8:AV8, "<>0")
Last edited by shg; 03-02-2018 at 03:34 PM.
Entia non sunt multiplicanda sine necessitate
COUNT should work actually even with formulas. Ah i guess shg have answer the reason why it does not work.
shg it works perfectly in the attached example, although my actual spreadsheet has many more rows....138. When I plug that formula it it reads way more than it should...not sure why....?
Maybe you should post a better example?
Due to privacy issues I can't post the original workbook and it would take way too much time to recreate the amount of info needed. Sorry!
Maybe
=COUNTIF(AK8:AV8, "<>0") - COUNTIF(AK8:AV8, "")
If that doesn't work, I don't have another suggestion.
shg....brilliant! Worked perfectly....thank you so much!!!!
Y'er welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks