Counting the same schedule date, problem..

[tokyojo1]

Hi guys looking for some help.

I have a production schedule that I want to track schedule adherence and attainment.

In Row E, we have a scheduled make date, the next cell down has an actual make date recorded by operators, this goes on for 100 lines, from e4, through e104, I want to know how many of these jobs were actually made on the scheduled date.

I am having issues because some of the scheduled dates can be scheduled on the same day, so if there is no dates recorded by the operators my formula still counts the scheduled dates. hope this makes sense.

I have attatched a sample of what i`m struggling with.


TIA

[vlady]

try something like this

=COUNTIFS($C$7:$C$28,"<>",$D$7:$D$28,D6)

[tokyojo1]

Thanks Vlady, is there any chance that this could be made to work with the attached file in the 1st post, it should only be saying two match.

[vlady]

try

=COUNTIFS($B$5:$B$40,"Actuals",$E$5:$E$40,E6)

and may i suggest that refrain from merging cells it can bring some troubles.

[tokyojo1]

Vlady Many thanks for the help, one more question, I have to copy this data form another sheet that in column E has a date and time format, the date function works great, operators only enter a date is there a way to tell the countifs function to ignore the time or to only look at the first two parts of the date.

Copied data looks like 1/12/2015 21:00
Can this countifs statement be made to look at only 1/12 and ignore the year and the time?

Thanks you.

[vlady]

you can use sumproduct something like the bellow

=SUMPRODUCT((MONTH(B1:B100)=2)*(YEAR(B1:B100)=2011)*A1:A100) -> month and year
the asterisk serves as an "AND"
B1:B100 are the dates/time so it depends on what you like

=SUMPRODUCT((Day(B1:B100)=5)*(MONTH(B1:B100)=2)*(YEAR(B1:B100)=2011)*A1:A100) day/month/year

Note that you can have reference cell instead of typing 5 for days 2 for month say you have a specific date
D1 = 2/2/2015 -> real date

=SUMPRODUCT((MONTH(B1:B100)=month(D1))*(YEAR(B1:100)=year(D1))*A1:A100) get the month in D1 and year compare to the dates in B1:B100

HTH