Solved

Countifs cannot see my error

Posted on 2016-09-21
5
76 Views
Last Modified: 2016-09-21
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
Comment
Question by:route217
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 125 total points
ID: 41808257
Hi,

Could you send an example file?

Regards
0
 
LVL 33

Expert Comment

by:Rob Henson
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 31

Accepted Solution

by:
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))

Open in new window

0
 
LVL 33

Assisted Solution

by:Rob Henson
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

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question