Mar 17 2021 01:07 PM
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
"<=" & Trans!$E3
It is the " " around <= and the & 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 ""...
How can I work around this?
Thx in adv
Mar 17 2021 01:15 PM
Mar 17 2021 02:40 PM
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.
Mar 17 2021 02:46 PM
Mar 17 2021 03:11 PM - edited Mar 17 2021 03:28 PM
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. :)
Mar 17 2021 04:12 PM
Mar 18 2021 01:51 AM
#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.