Countifs formula not working when referenced to another worksheet
I have attached a sample workbook which shows what I am trying to extract from sheet "Service Reminders 2014". In worksheet "Results" cells b11:e11 I am trying to extract the amount of vehicles with within age ranges provided that have a magic number attached which is pretty easy everything >0 is a proper magic number and also the amount of vehicles in each age range that have "Booked" associated within the range of "Service Reminders" Y2:AH5000, The formula works fine until I add the final criteria and then it give me a #VALUE! can anyone help with this i'm sure its something simple im doing wrong?
Thanks
You had problem here
=COUNTIFS('Service Reminders 2014'!J1:J4999,"<="&DATE(YEAR(TODAY())-0,MONTH(TODAY()),DAY(TODAY())),'Service Reminders 2014'!J1:J4999,">="&DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY())),'Service Reminders 2014'!E:E, ">=1")
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Re: Countifs formula not working when referenced to another worksheet
Thats great thanks alot, why would this one not be working then?
=COUNTIFS('Service Reminders 2014'!J:J,"<=" & DATE(YEAR(TODAY())-0,MONTH(TODAY()),DAY(TODAY())),'Service Reminders 2014'!J:J,">=" & DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY())),'Service Reminders 2014'!Y:AH,"Booked")
or
=COUNTIFS('Service Reminders 2014'!$K$2:$K$4999,"<=" & DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY())),'Service Reminders 2014'!$K$2:$K$4999,">=" & DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())),'Service Reminders 2014'!$Z$2:$AI$4999,"Booked")
Re: Countifs formula not working when referenced to another worksheet
I have attached a copy of the workbook with the formulas that are not working, The formulas are in sheet reminders, B12 - E12, Any suggestions would be most appreciated
Just when reviewing the sheet can anyone tell me why the sum off B11+C11+D11+E11=302 but another count as seen in A5 = 304 why would the count on the others be 2 short?
Thanks
Johnny
Re: Countifs formula not working when referenced to another worksheet
Can anyone tell me why the attached worksheet is not accurate, It is telling me that there are only 302 vehicles listed when I have a formula to count vehicles within a certain age group and I have a simple count if which tells me there are a total of 304 vehicles, I have also calculated all Booked vehicles by age and the total comes out @ 52 instead of me simple countif which counts 79, I have attached a sample workbook I'm just so confused now so a little help would be most appreicated.
Johnny Reminders.xlsx
Re: Countifs formula not working when referenced to another worksheet
The 302/304 discrepancy is because you have 2 rows (12 and 17) where there is a number >=1 in column E but no date in column J (just / / value), so those rows are included in the 304 but not included in any of your date ranges.
The 52/79 issue is because you haven't applied my suggested COUNTIF formula to every row in column AJ - presumably you added that formula while the data was filtered - remove all filters and apply again and you should get a closer match.
Re: Countifs formula not working when referenced to another worksheet
daddylonglegs thanks for that I was really confused, I did add the formula when a filter was applied feel so stupid now, I have sorted it but it is still saying 78 instead of 79 but if I do a sum on the column it comes out as 79, would the last formula for over 8 years be wrong?
It is =SUMIFS('Service Reminders 2014'!$AJ:$AJ,'Service Reminders 2014'!$J:$J,">="&EDATE(TODAY(),96))
LinkBack URL
About LinkBacks
Register To Reply
Re: Countifs formula not working when referenced to another worksheet
Bookmarks