+ Reply to Thread
Results 1 to 16 of 16

Countifs formula not working when referenced to another worksheet

  1. #1
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    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
    Attached Files Attached Files
    Last edited by Burt_100; 03-17-2014 at 11:23 AM.

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Countifs formula not working when referenced to another worksheet

    Kepp your ranges in the same lenght
    B11:

    =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'!E:E, ">=1")

    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")

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,234

    Re: Countifs formula not working when referenced to another worksheet

    its because you are using a range for the dates and then a column for the >1 statement
    change to

    =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'!E1:E4999, ">=1")

    instead of
    E:E

    and it works
    Wayne

    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.

  4. #4
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Countifs formula not working when referenced to another worksheet

    @etaf.
    Tha would fail if you drag down...

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,234

    Re: Countifs formula not working when referenced to another worksheet

    True
    thanks

    =COUNTIFS('Service Reminders 2014'!$J$1:$J$4999,"<="&DATE(YEAR(TODAY())-0,MONTH(TODAY()),DAY(TODAY())),'Service Reminders 2014'!$J$1:$J$4999,">="&DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY())),'Service Reminders 2014'!$E$1:$E$4999, ">=1")

  6. #6
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    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")

  7. #7
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Countifs formula not working when referenced to another worksheet

    Your last range covers more than 1column.

  8. #8
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Countifs formula not working when referenced to another worksheet

    Is there no way i can get around this then?

  9. #9
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Countifs formula not working when referenced to another worksheet

    Attach your workbook.

  10. #10
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    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

    Reminders.xlsx

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Countifs formula not working when referenced to another worksheet

    Do you mind have a "helper" column? If not then put this formula in AJ2 copied down

    =COUNTIF(Y2:AI2,"booked")

    and then use this formula for between now and 3 years ago

    =SUMIFS('Service Reminders 2014'!$AJ:$AJ,'Service Reminders 2014'!$J:$J,"<="&EDATE(TODAY(),0),'Service Reminders 2014'!$J:$J,">"&EDATE(TODAY(),-36))

    Note that I used EDATE to get the dates, it's shorter. that counts months so 5 years ago is =EDATE(TODAY(),-60) etc.

    Also you shouldn't have <= and >= in all the formulas otherwise you will be double counting on the boundary dates, so I used <= and >
    Audere est facere

  12. #12
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Angry 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

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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.

    ...also E12 formula is wrong, should be

    =SUMIFS('Service Reminders 2014'!$AJ:$AJ,'Service Reminders 2014'!$J:$J,"<="&EDATE(TODAY(),-96))
    Last edited by daddylonglegs; 03-18-2014 at 06:05 PM.

  14. #14
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

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

  15. #15
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Countifs formula not working when referenced to another worksheet

    Yes, I just edited my last reply - that formula should be

    =SUMIFS('Service Reminders 2014'!$AJ:$AJ,'Service Reminders 2014'!$J:$J,"<="&EDATE(TODAY(),-96))

  16. #16
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Countifs formula not working when referenced to another worksheet

    Thanks Most Appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Countifs and concatenate formula not working
    By ensmith in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2014, 03:50 PM
  2. Formula half working. IF( Countifs)
    By Unnefable in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-01-2013, 12:11 PM
  3. Countifs FOrmula Not working
    By cartica in forum Excel General
    Replies: 1
    Last Post: 10-30-2013, 01:34 PM
  4. [SOLVED] Excel 2007 : COUNTIFS using variable to identify column referenced
    By sarahaley in forum Excel General
    Replies: 17
    Last Post: 05-07-2012, 12:03 PM
  5. Vlookup formula referenced to another worksheet
    By savery101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-04-2009, 04:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.08125 seconds
  • Memory Usage 9,311KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (16)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (16)postbit_legacy
  • (16)postbit_onlinestatus
  • (16)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1