CODEWITHSUNDEEP
excelexcel-formulacountif
FoolzRailer
FoolzRailer

Reputation: 207

CountIF returns 0 with array and criteria in formula

I have a large array of data, consisting of start and end date/time for license use. I'm trying to count how many users are active at the same time.

I'm using the below formula, but I get a return value of 0 some places, which basically shouldn't be possible.

=CountIFs(E$2:E$5616;"<="&E1108;F$2:F$5616;">"&E1108)

Is the approach wrong or is there a better one?

Edit: Added extra screendump showing the 6 places I have a 0 returned. Have run a clean and trim on the data as well, with no changes to the output.

Edit2: Added npciture showing the difference between my two CountIf and Sumproduct outputs. Is there something I'm missing here, the data should be exactly the same so the output should be 1 and 2 for everything?

enter image description here

enter image description here

Upvotes: 0

Views: 745

Answers (2)

Rory
Rory

Reputation: 34075

Try using SUMPRODUCT instead of COUNTIFS, since it isn't susceptible to the same number coercion issues:

=SUMPRODUKT(--(E$2:E$5616<=E2);--(F$2:F$5616>E2))

Upvotes: 1

Error 1004
Error 1004

Reputation: 8230

Try:

=COUNTIFS(E$2:E$5616, "<=" & E1108, F$2:F$5616, ">" & E1108)

Replace , with ;

Upvotes: 0

Related Questions