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

A B C D On Time Early Late January February March April May June July August September October November December <colgroup><col><col><col span="2"></colgroup><tbody> </tbody>

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

And here is my Data:

U	W
Early	July
Late	August
On Time	August
Early	August
Early	September

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

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