Countif and Countifs error

[David Cerny]

I am trying to count concurrent users, but both COUNTIF and COUNTIFS are not calculating properly at (what seems like) random rows.

My COUNTIFS formula (in Column C) of'
=COUNTIFS($A$5:$A$10,"<="&A7,$B$5:$B$10,">="&A7)
is not working in Row 7 only.

I also tried COUNTIF formula (in Column D)of;
=COUNTIF($A$5:$A$10,"<="&A7)
which should count all Logins <= Row 7, but the result is only 2 (since Column A is sorted by time, Column D should increment by 1 each row)



For some reason, SUMPRODUCT works, but I need to convey this calculation to a web designer and the count functions are easier to understand.

I have uploaded the file for your help.

If anyone can help me out, it would be greatly appreciated.

David

 

[Nebu]

Hi:

I have used the formula as attached, is this what you are looking for?

Note: formulas in yellow cells
Thanks

 

[David Cerny]

Nebu,
Thank you, your formula works great (I do not fully understand it, but I can live with that).
Do you know why the COUNTIF(S) formulas were not working? It was randomly determining that a login time was not = to itself. I do need to convey the calculation to a programmer and COUNTIF(S) are easier to explain (when they work).

Thank you again,
David

 

[Nebu]

Hi:

I think it is something to do with array , the mmult in my formula is basically an array. I guess you were getting an error because you were using an non-array formula.

Thanks