COUNTIFS and date range selection = #VALUE! error

[MSrobinann]

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

[#2]

@MSrobinann 

That how the formula works, in this part it's correct

That's something else.

[#3]

@Sergei Baklan 

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.

[#4]

@MSrobinann 

In general it doesn't matter how many criteria you use

[#5]

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. :) 

[#6]

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

[#7]

@MSrobinann 

#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.