Trouble with Countifs formula. Is there significance to where you reference your data?

[willow1985]

Hello,

I seem to be having trouble with countifs formulas and not understanding why I am getting zero. I want the formula to count how many matches with "On Time", "Early", and "Late" based on the data in U and W (see chart and data example below)

If I make the data on the same sheet as the chart it works:

My formula for column B is: =COUNTIFS($U:$U,$B$1,$W:$W,A2)
My formula for column C is: =COUNTIFS($U:$U,$C$1,$W:$W,A2)
My formula for column D is: =COUNTIFS($U:$U,$D$1,$W:$W,A2)

However as soon as I change it to reference the data on another sheet it gives me a result of 0:

My formula for column B is:=COUNTIFS('Time Tracker'!$U:$U,$B$1,'Time Tracker'!$W:$W,A2)

Here is my chart that will have the formula

[TABLE="width: 309"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD] [TABLE="width: 309"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]On Time[/TD]
[TD]Early[/TD]
[TD]Late[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


And here is my Data:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]U[/TD]
[TD="class: xl65, width: 64"]W[/TD]
[/TR]
[TR]
[TD]Early[/TD]
[TD]July[/TD]
[/TR]
[TR]
[TD]Late[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD]On Time[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD]Early[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD]Early[/TD]
[TD]September[/TD]
[/TR]
</tbody>[/TABLE]

Note: the Early, Late and On time are determined by formulas as well as the Months.

Any insight would be greatly appreciated as I am stumped

Thank you!

 

[Anthony47]

I didn't experience any problem when referring data onto another worksheet... Could you share a sample workbook where the problem exists?

Bye

 

[willow1985]

I do not know how to share a workbook on here.... Sorry still new to using this forum..

 

[Fluff]

You cannot upload files to the site, but you can upload to a share site such as OneDrive, GoogleDrive, DropBox. Mark for sharing & then post the link to the thread.

Are your months just text, or are they dates formatted to just show the month?

 

[willow1985]

Ok I think I figured it out....I uploaded the workbook to dropbox. Here is the link: https://www.dropbox.com/s/t2wksn5pgzew3wp/Performance Tracker.xlsx?dl=0

Let me know if this works. The formula that does not work is in yellow

 

[Fluff]

Col W is dates not months, try changing the formula to
=TEXT(DATE($V$1,1,-2)-WEEKDAY(DATE($V$1,1,3))+V4*7,"mmmm")

 

[willow1985]

Thank you so much! Works perfect now!

 

[Fluff]

You're welcome & thanks for the feedback