Countifs to count # of occurences per day by, using a date as criteria

[Khoward866]

Hello,

I am trying to count the number of occurrences (i.e. # of flights) per day based on the date and location. The table that the information is populating is laid out in a monthly fashion so row "A" is filled with the days of the month. I have tried to use the following countifs statement in the past and it has worked great but now it doesn't seem to want to calculate. =COUNTIFS(DATA!$Q:$Q,"OLUA",DATA!$M:$M,'TABLE (DAILY)'!C24), Data is my raw data sheet which is ran from a MIS on a daily basis, Table is the calculation range, and C24 is the date. Below are examples of my two sheets. Any help would be great even if it is another way of doing it entirely.

DATE LOCATION
1/1/2018 FLORIDA
1/3/2018 LA



[TABLE="width: 353"]
<colgroup><col width="162" style="width: 122pt; mso-width-source: userset; mso-width-alt: 5924;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;" span="4"> <tbody>[TR]
[TD="class: xl12166, width: 162, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl12153, width: 42, bgcolor: white"] [/TD]
[TD="class: xl12167, width: 62, bgcolor: blue"]01-Dec[/TD]
[TD="class: xl12167, width: 51, bgcolor: blue"]02-Dec[/TD]
[TD="class: xl12167, width: 51, bgcolor: blue"]03-Dec[/TD]
[TD="class: xl12167, width: 51, bgcolor: blue"]04-Dec[/TD]
[TD="class: xl12167, width: 51, bgcolor: blue"]05-Dec[/TD]
[/TR]
[TR]
[TD="class: xl12154, bgcolor: white"] [/TD]
[TD="class: xl12153, bgcolor: white"] [/TD]
[TD="class: xl12164, bgcolor: #D9D9D9"]Tue[/TD]
[TD="class: xl12165, bgcolor: #D9D9D9"]Tue[/TD]
[TD="class: xl12165, bgcolor: #D9D9D9"]Wed[/TD]
[TD="class: xl12165, bgcolor: #D9D9D9"]Thu[/TD]
[TD="class: xl12165, bgcolor: #D9D9D9"]Fri[/TD]
[/TR]
[TR]
[TD="class: xl12149, bgcolor: blue"] [/TD]
[TD="class: xl12148, bgcolor: blue"]Tot[/TD]
[TD="class: xl12150, bgcolor: blue"]1[/TD]
[TD="class: xl12151, bgcolor: blue"]2[/TD]
[TD="class: xl12151, bgcolor: blue"]3[/TD]
[TD="class: xl12151, bgcolor: blue"]4[/TD]
[TD="class: xl12151, bgcolor: blue"]5[/TD]
[/TR]
[TR]
[TD="class: xl12158, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl12161, bgcolor: #FFFF99"]0[/TD]
[TD="class: xl12155, bgcolor: white"] [/TD]
[TD="class: xl12155, bgcolor: white"] [/TD]
[TD="class: xl12155, bgcolor: white"] [/TD]
[TD="class: xl12155, bgcolor: white"] [/TD]
[TD="class: xl12155, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl12159, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl12162, bgcolor: #FFFF99"]0[/TD]
[TD="class: xl12156, bgcolor: white"] [/TD]
[TD="class: xl12156, bgcolor: white"] [/TD]
[TD="class: xl12156, bgcolor: white"] [/TD]
[TD="class: xl12156, bgcolor: white"] [/TD]
[TD="class: xl12156, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl12159, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl12162, bgcolor: #FFFF99"]0[/TD]
[TD="class: xl12156"]0[/TD]
[TD="class: xl12156"]0[/TD]
[TD="class: xl12156"]0[/TD]
[TD="class: xl12156"]0[/TD]
[TD="class: xl12156"]0[/TD]
[/TR]
[TR]
[TD="class: xl12160, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl12163, bgcolor: #FFFF99"]0.0[/TD]
[TD="class: xl12168"] [/TD]
[TD="class: xl12157, bgcolor: white"] [/TD]
[TD="class: xl12157, bgcolor: white"] [/TD]
[TD="class: xl12157, bgcolor: white"] [/TD]
[TD="class: xl12157, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl12152, bgcolor: blue"] [/TD]
[TD="class: xl12148, bgcolor: blue"]Tot[/TD]
[TD="class: xl12150, bgcolor: blue"]1[/TD]
[TD="class: xl12151, bgcolor: blue"]2[/TD]
[TD="class: xl12151, bgcolor: blue"]3[/TD]
[TD="class: xl12151, bgcolor: blue"]4[/TD]
[TD="class: xl12151, bgcolor: blue"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

Assuming your raw data is in the correct columns and there are actual lines which needs to be counted based on the selected criteria, there are a few things you need to check:
1. did you spell the location typed in the formula correctly; and
2. are the date fields in the raw data and table sheet in the same format( e.g. is the field an actual date/time field filled with a MS serial number or is it a plain text-field filled with the formatted date); and

if those things are checked your formula should work.
The above example with a report table on December and raw data from January will always come up 0.

 

[Khoward866]

Thank you for you input, the spelling is correct and both formats are the same that is why i am confused. The dates I typed in were for a different month but both the table and data include December data. It was working for a few minutes but when i tried to test it by inserting new information it just stopped.