Hi, I have a plain text column of numbers all with four digits to the right of the decimal point. I want to count every cell in the column where those four digits are not 0000
I tried this but failed:
=countif(C1:C500,right(C1:C500,4)<>"0000")
Can anyone help please?
Thank you, Chris
I think we need to see whats REALLY in those cells... are they formatted to look like 4 dps, etc.
Refere to the yellow banner (top) and post a small sample Excel file.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
On reflection, try this:
=SUMPRODUCT(--(C1:C500=INT(C1:C500)))
13.0830
13.1100
14.0900
14.0950
15.0000
15.0000
15.0000
16.0000
16.0000
16.0000
17.0615
17.1120
17.1808
Thanks Glenn. I will try it
Chris
In the example above there are 7 cells that don't end in .0000
Do try it, with your real data... as I'd asked for an Excel sheet, not a copy/paste job.
I want to count cells that don't end in 0000, not add them together.
Thanks Glenn, if you need sheet, let me figure out how to supply one from my phone here. I did try your formula though and got a #value error
The formula does NOT sum them. It counts them.
Did you TRY it???
See file.
Yes I tried it but got the value error. I think the issue may be as I said, it's plain text format done to cause the following zeros to display rather than hide them.
So, please do as I asked at Post #2 and post an EXCEL sheet. I need to see what is REALLY in those cells.
Thank you I will do so when I am back on my laptop on Monday. I can't do it from my phone.
OK. No problem!
Hi Glenn,
I have uploaded a sheet for you. Thanks
Chris
Try this:
=SUMPRODUCT(--(--SUBSTITUTE(C1:C80,"-","")=INT(--SUBSTITUTE(C1:C80,"-",""))))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Well, that is a TOTALLY different problem. When did you tell us about the hyphen and the remaining digits in the cell.
Alis works, of course
An alternative would be
=SUMPRODUCT(--((--LEFT(C1:C80,7))<>(--LEFT(C1:C80,2))))
for NOT EQUAL to, and
=SUMPRODUCT(--((--LEFT(C1:C80,7))=(--LEFT(C1:C80,2))))
for EQUAL to.
Hiwever, can entries like this occur:
12.0000-1630 where the first numeric substring ends in 0000 ? Then Ali's formula & mine give different results. Which is correct, I do not know, as you did not specify that condition.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks