Countif is not working when cell contains text and numbers

[Aurora]

Ill sum up the issue as simply as I can, please let me know if you need more information.

I have a cell column that is referencing two dates to gather the amount of days between them. That formula is as follows - =IF(B2=0, "", DATEDIF(B2,C2,"d")+1 & " Days")

B2 and C2 are starting date and ending date.

I am trying to count cells that are greater than a specific number. As an example, I want to tally up all cells that are greater than 20 days. The formula I am trying to use is - =COUNTIFS ($Page2.D2:$Page2.D1201, ">=20", $Page2.N2:$Page2.N1201, "Dog")

D2:D1201 is the range of cells that the first formula is in, and for full transparency of the formula N2:N1201 is just a separate condition to the countif function.

I have attempted many different ways of doing things including wildcards and the full “20 Days” but its not accurate.“20 Days” Gives me way too many results. “20” Gives me an empty result. Anything single digit, 1 to 9, counts every cell regardless. The only way Ive been able to get results that I assume are accurate is if I remove the & " Days" Function but Im hoping to keep the text addition in as it makes things much easier on the eyes to see at a glance what its referencing.

I’m sure the answer is simple but Im not too experienced in this type of thing and just flying by the seat of my pants.

[LeroyG]

Maybe you can set the number cell format code as [<0]-0;[>0]0" Days";Estandar.

[Aurora]

@LeroyG

Maybe you can set the number cell format code as [<0]-0;[>0]0" Days";Estandar.

@LeroyG If you are referencing adding that code to the format cells option and replace “General” in the D column, I did so but nothing changed. I will try to do some googling to educate myself on what the format code you listed does, perhaps Im misapplying it.

[robleyd]

Does this help?

Number Format Codes

[Wanderer]

@Aurora

I did so but nothing changed

Did you only apply the format or also remove your & " Days"?
.
Remove your string first to get the right results for COUNTIF. Then set the formatting, so the cell shows " Days" but the text will be no part of the content. So it can be used for calculations.

[Villeroy]

@Aurora

DATEDIF(B2,C2,“d”)+1

is the same as C2-B2+1. Just keep that number(s). Don’t concatenate numbers with text.

[Ash733]

@Aurora

That formula is as follows - =IF(B2=0, “”, DATEDIF(B2,C2,“d”)+1 & " Days")

This formula results in text because you append it with " Days", thus the test for “>=20” won’t work. In a spreadsheet where you’re calculating things it’s better practice to put something like “Days” in the heading column for that row; this way you know it’s days but the value remains calculable (a number).

You can append text like " Days" on a report sheet that obtains values from your calculable sheets in order to read correctly.

You could convert the text back into a number with something like =LEFT and/or VALUE

[Aurora]

@robleyd

Does this help?

Number Format Codes

@robleyd I am sure that will help me to understand exactly whats going on with the format code above. I read through it although I’m still confused so Ill have to try again when my migraine is gone. Thanks for linking it!

@Wanderer

Did you only apply the format or also remove your & " Days"?
.
Remove your string first to get the right results for COUNTIF. Then set the formatting, so the cell shows " Days" but the text will be no part of the content. So it can be used for calculations.

@Wanderer Thanks for the further clarification, I tried to finagle the format code but I didnt think about removing the & " Days" from the original formula. It seems like this method has solved my issue. I dont think I would have understood it without your input, but I suppose the technical solution to this problem is LeroyG. My only further question is the “;Estandar.” part of the format code. I cant tell if its a fancy code, just the word standard in another language, or perhaps its LeroyG’s signature at the end of messages. It doesent seem to impact the format itself.

[Aurora]

@Villeroy

is the same as C2-B2+1. Just keep that number(s). Don’t concatenate numbers with text.

@Villeroy You seem to be correct, its probably smarter to do it this way so Ill look into this further. From what I understood at the time the Datedif works great for the sake of counting days “d”, Months “m”, years “y”, but it might not be needed for this as your simpler formula works. I plan to incorporate another formula that ignores weekends and holidays in the calculation so I just need to make sure it doesent impact that before making the full change.

@Ash733

This formula results in text because you append it with " Days", thus the test for “>=20” won’t work. In a spreadsheet where you’re calculating things it’s better practice to put something like “Days” in the heading column for that row; this way you know it’s days but the value remains calculable (a number).

You can append text like " Days" on a report sheet that obtains values from your calculable sheets in order to read correctly.

You could convert the text back into a number with something like =LEFT and/or VALUE

@Ash733 You’re totally right about that and honestly it would be so much easier to just have simpler data like that. I just spent 30 hours creating new cells to input data I previously had by colour coding the background of cells. Turns out background colours arent capable of being countif formulad.

The only issue with pure data is it can make it hard on the eyes. I will be looking at this sheet often so theres a lot of value in knowing whether a number is referencing a time, date, price, or percentage. Fortunately the rest of my data is now pure number or text and has not yet been an issue. The more time I spend in this program though the more I realize the importance of being as simple as possible, never too late to learn.

I’ll do some research on the =LEFT and/or VALUE aspect of your comment as I havent heard about that before.

Not sure if @ ing during replies is necessary or annoying, apologies if so.

[Wanderer]

@Aurora

Not sure if @ ing during replies is necessary or annoying

No problems here: Everybody, who wrote in this thread, is notified of answers. We see in the notification, if we are cited or named. If we don’t wish notifications, we can switch them off in out profiles or reduce them…

[LeroyG]

@Aurora

pure data is it can make it hard on the eyes

You can add some conditional formatting to ease the visualization.