+ Reply to Thread
Results 1 to 8 of 8

Countifs using reference in cell

  1. #1
    Registered User
    Join Date
    12-01-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    16

    Countifs using reference in cell

    Hi,

    I am trying to use the Countifs function in order to count cells based on two conditions, 1 being in a cell and 1 a specific word - I have tried the below but I am coming up with a #value! error.

    =COUNTIFS(November!E9:E377, "=" & Summary!E7,November!G9:AJ377,"HOL")

    Is anyone able to offer some advice please.

    Many thanks

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Countifs using reference in cell

    Try:

    =COUNTIFS(November!E9:E377,Summary!E7,November!G9:AJ377,"HOL")
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Countifs using reference in cell

    Sorry... senior moment:

    SUMPRODUCT((November!E9:E377=Summary!E7)*(November!G9:AJ377="HOL"))

  4. #4
    Registered User
    Join Date
    12-01-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    16

    Re: Countifs using reference in cell

    This amazing thank you so much! I have never seen the SUMPRODUCT function before I will be looking more at this

    Is it possible that the formulae could be changed however ?

    If I use the below
    =SUMPRODUCT((November!E$9:E$377=Summary!E7)*(November!G$9:AJ$377="HOL"))+SUMPRODUCT((November!E$9:E$377=Summary!E7)*(November!G$9:AJ$377="1/2H"))

    This returns 12, 10 from HOL & 2 from 1/2H - however I would like this to return 11 as 1/2H equals 0.5 in terms of this sheet.

    Is this possible ?

    Many thanks again.
    Demo

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Countifs using reference in cell

    Demo,

    Given conditions are mutually exclusive, you could combine into one SUMPRODUCT - e.g.:

    =SUMPRODUCT((November!E$9:E$377=Summary!E7)*((November!G$9:AJ$377="HOL")+(November!G$9:AJ$377="1/2H")/2))

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Countifs using reference in cell

    =SUMPRODUCT((November!E$9:E$377=Summary!E7)*(November!G$9:AJ$377="HOL"))+0.5*SUMPRODUCT((November!E$9:E$377=Summary!E7)*(November!G$9:AJ$377="1/2H"))

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Countifs using reference in cell

    Go with XLENT's. Neater and tidier.

  8. #8
    Registered User
    Join Date
    12-01-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    16

    Re: Countifs using reference in cell

    Apologies for the late response.

    You gents are amazing thank you very much for your assistance!

+ 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. COUNTIFS - Multiple Conditions with Cell Reference
    By Epilogue in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2020, 07:29 PM
  2. [SOLVED] CountifS for Column Reference, Row Reference and Data Validation Reference
    By pavanbhoyar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-24-2018, 02:37 AM
  3. Countifs - Add 1 to Cell Reference
    By jbolduc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-31-2017, 12:44 PM
  4. [SOLVED] Sumproduct to replace countifs as countifs don't work on external source reference
    By KrishnaSagar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2017, 02:33 AM
  5. COUNTIFS cell reference problem
    By Nomad1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-27-2015, 10:33 AM
  6. countifs not working with cell reference
    By Trig79 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-18-2013, 07:07 AM
  7. Countifs to get criteria from a cell reference
    By SeanKosmann in forum Excel General
    Replies: 2
    Last Post: 01-27-2011, 11:00 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1