Countifs not counting correctly

[Marmalade20]

Hi,

I have a spreadsheet which I'm using for audit action tracking, I have a formula in place to calculate how many days overdue actions are which appears to be working fine returning results as "x Over Due"
I'm trying to create a simple pie chart on a separate tab to show how many are within 30 days over due, how many within 60 days over due and how many within 90 days over due. I've used the following formulas:
=COUNTIFS(Audit!R4:R200,">0*",Audit!R4:R200,"<=30*")
=COUNTIFS(Audit!R4:R200,">30*",Audit!R4:R200,"<=60*")
=COUNTIFS(Audit!R4:R200,">60*",Audit!R4:R200,"<=90*")

Within 30 days is returning a result one less than it should, within 60 is returning the correct result and within 90 is returning a result one more than it should.
I've managed to work out which cell it is counting by reducing my cell range and it's counting a cell containing "8 Over Due" in the third formula (60-90) rather than the first (0-30.)

Dow anyone have any idea why and how to correct this please?

Thanks.

 

[philaugust2004]

check there are no hidden characters in the cell

 

[S.H.A.D.O.]

Welcome to the board Marmalade20,

Have you tried it without the *?

 

[Marmalade20]

Welcome to the board Marmalade20,

Have you tried it without the *?

Thank you, I've often lurked but finally joined so i could post.

Without the * brings everything back as 0 (I'm assuming as it's a combination of numbers and text in the cells as it displays with the number followed by Over Due)


May sound daft here but how do I check for hidden characters in a cell?

Thanks.

 

[FormR]

Hi, welcome to the forum!

I have a formula in place to calculate how many days overdue actions are which appears to be working fine returning results as "x Over Due"

Is this the column you are trying to do the COUNTIFS() on? If so, does the formula literally return the text "x Over Due" - if it does, you should change it to return "x" only and use a custom format to display the Over Due text for example custom format those cells as 0 "Over Due".

Your COUNTIFS() would then be:

=COUNTIFS(Audit!$O$4:$O$200,">0",Audit!$O$4:$O$200,"<=30")
=COUNTIFS(Audit!$O$4:$O$200,">30",Audit!$O$4:$O$200,"<=60")

Etc..

 

[Marmalade20]

Hi, welcome to the forum!



Is this the column you are trying to do the COUNTIFS() on? If so, does the formula literally return the text "x Over Due" - if it does, you should change it to return "x" only and use a custom format to display the Over Due text for example custom format those cells as 0 "Over Due".

Your COUNTIFS() would then be:

=COUNTIFS(Audit!$O$4:$O$200,">0",Audit!$O$4:$O$200,"<=30")
=COUNTIFS(Audit!$O$4:$O$200,">30",Audit!$O$4:$O$200,"<=60")

Etc..

That's worked,thank you!