Share via

COUNTIFS formula stopped working

Anonymous
2022-02-16T15:52:23+00:00

I've been using this COUNTIFS function to count how many times a time range shows up in timestamp data I have collected.

Here is the formula: =COUNTIFS(E:E,">=12:00:00 PM",E:E,"<1:00:00 PM")

This has been working up until recently.

I discovered the issue when I copy and pasted in a new set of data into this "template" file I set up.

I've narrowed the issue down to copying and pasting data from one excel document to another. The cell formatting is the same, Time - 00:00:00 AM.

But if I hand type in the data I get the counts. The copy and pasted data do not throw counts. I get a "0". This is only a problem with the Time data. The CountIFS works fine with the date data.

I've attached an example of the difference in results I'm getting from copying and pasted data versus hand entered/typed in data.

Thanks for any help you can provide.

Microsoft 365 and Office | Excel | For business | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2022-02-16T16:30:28+00:00

@AP.... Most likely, the data is text, not numeric. Use formulas of the form =ISTEXT(D1) to confirm. The format of the cell does not matter; and looks can be deceiving.

You might be able to correct the problem by selecting a vertical range and using the Text To Columns feature.

If that does not work, there are issues that are difficult for us to "see" in screenshots.

Possibly, the data contains nonbreaking spaces (ASCII 160) or tab characters (ASCII 9). So, entering formulas of the form

=TRIM(SUBSTITUTE(SUBSTITUTE(D1, CHAR(160), CHAR(32)), CHAR(9), CHAR(32)))

in a parallel range, then copy-and-pasting-value back into the original range might correct the problem.

Otherwise, it would be best to upload an example Excel file to a file-sharing website, and post the download URL in a response here. I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it shares the same login as this forum.

Was this answer helpful?

2 people found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-02-17T22:04:45+00:00

    The data was text! Thanks for the tip on how to confirm that. I was able to fix the issue using the Text To Columns feature.

    Was this answer helpful?

    0 comments