Forum Discussion
COUNTIFS and date range selection = #VALUE! error
Nope, not when you are doing a 2nd criteria too. What I am doing is:
Count how many times is a specific item entered during a date range less than or = 03/10/21 i.e. 44265
When I run thru the Function Arguments all the criteria-answers are correct - except for the Criteria2. it put "" around the answer.
Btw, I am using the Excel365 version on Windows10.
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:
"<=" & Trans!$E3
and it shows "<=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 & 4 days between, and run of series of items counts for the selected day as an control in regards to an upcoming implementation/upgrading of an accounting system. Even though the logic is hard to see could that be the problem not having the selections dates in separate cells?Btw, thank you for your swift replies. 🙂
#VALUE! error could appear if you use in COUNIFS() ranges of different size as in third formula here
Showing of number when formula is evaluated that's how it shall be, dates in Excel are actually integer numbers starting from 1 which is Jan 01, 1900.