Countifs cannot see my error

Hi Experts using excel 2013

I have the following countifs formula which is returning 0 for July 2016 when in fact the correct answer is 17
=COUNTIFS('Master Data'!$R$2:$R$1000,AH$101,'Master Data'!$B$2:$B$1000,$AC100)

AC = Company Name
AH is 7 for month July
route217Asked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
If column R has dates and AH101 has the month number, try it like this and see if that works for you....
=SUMPRODUCT((MONTH('Master Data'!$R$2:$R$1000)=AH$101)*('Master Data'!$B$2:$B$1000=$AC100))

Open in new window

0
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

Could you send an example file?

Regards
0
 
Rob HensonFinance AnalystCommented:
Does column R in Master Data contain just months or full dates?

If it is full dates, then looking for 7 will indeed produce 0, a date with serial number 7 was 7 Jan 1900.

Thanks
Rob
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Also, should there be a row difference for the Date value (AH101) and the Company value (AC100)?

Thanks
Rob
0
 
route217Author Commented:
Thanks experts for the excellent feedback solved the problem....with your guidance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.