I am trying to count how many cells do not start with U, D, or R, appear before 15:00 pm and match a certain date (A4). I am using this function: =COUNTIFS(Sheet1!$B$2:$B$1088,A4,Sheet1!$C$2:$C$1088,"<>U*",Sheet1!$C$2:$C$1088,"<>D*",Sheet1!$C$2:$C$1088,"<>R*",Sheet1!$D$2:$D$1088,"<15:00")
Column B in Sheet1 contains all the dates, column C in Sheet1 contains the cells that either start with U, D, R, or something else, and column D in Sheet1 contains the time for when they appear. This exact function works in other workbooks of mine and returns the correct value but is returning a 0 in this workbook. Any help would be appreciated! Thanks in advance.
3 Answers
I've observed that most of the time COUNTIFS unable to produce correct result, so that I would like to suggest an alternate formula SUMPRODUCT.
Formula in cell F16:
=SUMPRODUCT((sheet1!A16:A23=$F$15)*(Sheet1!C16:C23<TIME(15,0,0)*(Sheet1!B16:B23<>"U")*(Sheet1!B16:B23<>"D")*(Sheet1!B16:B23<>"R")))
:Caveat:
- For me the COUNIFS formula in cell F17 is also working:
N.B.
But I would like to recommend the SUMPRODUCT, is better & never miss fires.
You may adjust cell references in the formula as needed.
I filled the data range with non-complying data, then applied your formula (copied and pasted). It returned 0, as it ought. I then began replacing data with complying sets, varying by the first letter in column C and it continued to work perfectly until the entire range was filled with complying data and it returned 1087.
There is no apparent error in your formula when examining it, and it works beautifully in practice, not to mention it works elsewhere for you.
That points to the data itself. First thought with data is always "How did it get here?" If imported, especially if web-scraped, it is very suspect as importing in general, either Excel just opening a CSV file, using the old wizard or the nice Power Query, copying and pasting, or especially web-scraping. Web-scraping and copy and paste often bring unworthy formatting while regular import is still of data one did not create or vet for source peculiarities himself. The former is obvious, while the latter is prone to things like a date string not being resolvable by Excel and therefore not being regarded as dates.
That last seems a likely suspect. Either of columns B and D might be affected by this kind of thing. But especially column D: times can be funny, especially if read out by someone who does not know (or care) how you need to use them. Not to mention web-scraping them along with unseen screen formatting characters that make Excel see them as "not-a-date or time" but they look GOOD in the column and you think they DID import.
You present no data so we cannot help there. But you can do it yourself. Look with an open mind. Anything suspect, directly format the cell as whatever, a time perhaps, then Clear Contents and directly type the information into the cell. Or press Ctrl-Shift-: to enter a perfect time and edit it to be the cell's old value. Now you know it's a time for sure, and can alter the corresponding data to make the row comply, then see if the formula has changed from 0 to 1. So examination and logical testing. Also, press F2 on some of it and Left Arrow back through it watching carefully for a press of the key that does not move the cursor left a character... 'cause it did move left a character: one of the unseen characters, present but unseeable to our eyes. Apparent to Excel though.
Lots of other techniques to examine it. Also, while this seems unlikely, I ought to mention that to COUNTIFS() an uppercase "U" is the same as a lowercase "u" so if the "some other letter" description means they all start with U, D, or R, just some are uppercase, some lowercase, then you'd see all rows fail because the function sees all the rows as not meeting the column C conditions. Seems rather unlikely that is it though.
Final bigger thought is unlikely since you have pretty particular range (2:1088!!!) but if your real range is somewhat to much to very much larger, consider the possibility that the data in 2:1088 is simply older data that none of matches the criteria so 0 is the correct result.
When you compare a time value with a text string, the time value will always be treated as "less" than the text string, even if the text string supposedly represents a time value.
Note that the formula in column D returns TRUE for every row. But the formula in column E returns FALSE for the last row, as you would expect.
As such, simply replace the condition referencing 15:00 at the end of your COUNTIF, such that your formula is as follows:
=COUNTIFS($A$2:$A$9,$H$1,$B$2:$B$9,"<>U*",$B$2:$B$9,"<>D*",$B$2:$B$9,"<>R*",$C$2:$C$9,"<"&TIME(15,0,0)) Credit and +1 to @Rajesh_S for the sample data, who has also offered a good and solid alternative with SUMPRODUCT.
Regardless of which method you prefer, you must use the TIME(hour,minute,second) function to make comparisons with cells containing times.
