Countifs includes dates in different format

Hi,

I'm trying to do COUNTIFS with criteria range [long date] and criteria [short date] but its not working
I have tried to change the date format of the [long date] to short but it is still shows the hour if I stand on the cell.

I have tried to use * after the [short date] cell but still the function does not work.
I manage to change the extract just the cell format using [=DATEVALUE(DAY(C2) & "/" & MONTH(C2) & "/" & YEAR(C2))] but I don't want to add more data to the DB.

Plus I'm using this function for making count by hour graph [=SUMPRODUCT(--(HOUR(A$2:A$13)=HOUR(C2)))] how can I add this function to count only if a different =X
so I will have graph that shows how many item there were by person X on hourly frame

please help me finding the solution...

Thanks!

COUNTIF() and COUNTIFS() don't handle arrays, other than straight ranges.
Try this where A1:A20 contains your mixed dates and B1 your target date in any format

Formula:

Please Login or Register  to view this content.

Can you adapt that to suit your needs?

I can't see why you have dates with times in Column A and how it relates to Column B.

I don't understand what you need in Column H can you try to explain a little better?
Does this relate to Column A or B ?

See if this workbook helps
Select from the drop-downs in L1 & M2

I don't see clearly what you mean by "Sum how much times sales person work on hour of day"

If it means if a time appears in Column B against a sales person in Column E this counts as one, then COUNTIFS() will work.
None of the criteria depends on Column A.

I have added what I assume you mean to this workbook.
Select from the drop-downs in the yellow cells.

I'm at a total loss from the information so far, as to why you have "long dates" in Column A.
Seems to me that if you were to strip the time element from this column then all your problems could be solved with COUNTIFS() and/or SUMIFS().