Solved

# Countifs cannot see my error

Posted on 2016-09-21
41 Views
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
0
Question by:route217

LVL 48

Assisted Solution

Rgonzo1971 earned 125 total points
ID: 41808257
Hi,

Could you send an example file?

Regards
0

LVL 31

Expert Comment

ID: 41808273
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

LVL 28

Accepted Solution

Subodh Tiwari (Neeraj) earned 250 total points
ID: 41808414
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))
``````
0

LVL 31

Assisted Solution

Rob Henson earned 125 total points
ID: 41808505
Also, should there be a row difference for the Date value (AH101) and the Company value (AC100)?

Thanks
Rob
0

Author Comment

ID: 41808676
Thanks experts for the excellent feedback solved the problem....with your guidance.
0

## Featured Post

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…