COUNTIFS and date range selection = #VALUE! error

Question

Hi,I am working on a spreadsheet where I am counting the number of times a specific item has been entered during a specific period. Everything is fine until I enter the range selection&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"&lt;=" &amp; Trans!$E3&nbsp;It is the " " around &lt;= and the &amp; that causes the problem because it now considers the outcome to be a text string and not a date value. It shows the correct date number i.e. 44265 but it is in ""...&nbsp;How can I work around this?&nbsp;Thx in adv

sergeibaklan · Answer

MSrobinann&nbsp;
That how the formula works, in this part it's correct

That's something else.

msrobinann · Answer

SergeiBaklan&nbsp;&nbsp;Nope, not when you are doing a 2nd criteria too. What I am doing is:&nbsp;Count how many times is a specific item entered during a date range less than or = 03/10/21 i.e. 44265&nbsp;When I run thru the Function Arguments all the criteria-answers are correct - except for the Criteria2. it put "" around the answer.&nbsp;Btw, I am using the Excel365 version on Windows10.

sergeibaklan · Answer

MSrobinann&nbsp;
In general it doesn't matter how many criteria you use

msrobinann · Answer

I wish I could do a screen dump but I unfortunately am working on a client's data.I know it shouldn't matter but I am still getting a #VALUE! error. And yes, I have checked the formatting of the Date-column.Criteria_range1 is the area with all the items nos. - plenty of duplicates, and Criteria1 is cell reference to a specific item.Criteria_range2 is the area with all the dates per item line no., and Criteria2 is:"&lt;=" &amp; Trans!$E3and it shows "&lt;=44265" in the Function Arguments...I have noticed that you are running the selections dates in separate cells. I don't do that as this spread sheet is based on control of numbers. So in a worksheet I select 10 items with 3 &amp; 4 days between, and run of series of items counts for the selected day as an control in regards to an upcoming implementation/upgrading&nbsp; of an accounting system. Even though the logic is hard to see could that be the problem not having&nbsp;the selections dates in separate cells?&nbsp;Btw, thank you for your swift replies. 🙂&nbsp;&nbsp;

msrobinann · Answer

I can run each criteria in its own cell, and they work fine. Combining them apparently is the problem...

Forum Discussion

COUNTIFS and date range selection = #VALUE! error

6 Replies

Resources