COUNT Function Excel

Question

I have the following table:

oDate                 oValue
-------------------------------
2019-01-01 00:00      90
2019-01-01 00:30      90
2019-01-01 01:00      86
2019-01-02 00:00      86
2019-01-02 00:30      90
2019-01-02 01:00      86
2019-01-02 01:30      90

I want to have the following result:

oDate          90     86
-----------------------------------
2019-01-01     2      1
2019-01-02     2      2

I want to have the count of each oValue for each different oDate. Does anyone have the idea for this case?

Thank you.

Answer 1

COUNTIFS as given in another answer in my opinion is the way to go. However if you are interested in seeing other potential methods, you could do this with SUMPRODUCT. The reason I advise against it is as the data gets large, you can wind up doing an extrodinary number of calculations. Also with SUMPRODUCT, since it is performing array calculations, full column references should be avoided. Where as with COUNTIFS full column references wont make a difference.

So with SUMPRODUCT and assuming the layout in the picture below, use the following formula in C13 and copy down and to the right as required.

=SUMPRODUCT((INT($B$4:$B$10)=$B13)*($C$4:$C$10=C$12))

Adjust the ranges to suit your data.

Answer 2

Put this under the 90 in the second table then drag right and down.

=COUNTIFS($A:$A, ">="&$D2, $A:$A, "<"&$D2+1, $B:$B, E$1)