+ Reply to Thread
Results 1 to 5 of 5

Countifs across a range

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    WATFORD
    MS-Off Ver
    16.16.5
    Posts
    3

    Countifs across a range

    Hi

    I'm fairly new to excel and have been teaching myself via online help.

    I'm trying to figure out how to calculate a total across a large range and on two seperate tabs. I'm using Countifs and keep getting '#VALUE' with the following formula:

    Below is a small example however the actual data sheets consists of 50 columns and over 300 rows

    Untitled.png

    I need to find the text 'Yes' in all columns and count it firstly and then ensure that it's only finding the 'Yes' answer for Store A.

    The formulas I thought would work is countifs:

    =COUNTIFS(C3:E3,"YES",B3:B8,"STORE A")

    Any advice??? Would I need to used index match formulas instead?

    Many thanks in advance for any assistance.
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,567

    Re: Countifs across a range

    There are at least 2 ways you could do that

    =SUMPRODUCT((C3:E3,="YES")*(B3:B8="STORE A"))

    =COUNTIF(INDEX(C3:C8,MATCH("STORE A",B3:B8,0),0),"YES")

    As you're trying to teach yourself, I'll leave you to figure out how each one works, but if there is anything you don't understand, please ask

  3. #3
    Registered User
    Join Date
    01-11-2019
    Location
    WATFORD
    MS-Off Ver
    16.16.5
    Posts
    3

    Re: Countifs across a range

    Thanks, Jason. I found that the Index Match works if both the tables are on the same sheet however I get the same error value when I move the tables on two separate sheets on the same workbook. Also realised why initially the Index Match wasn't working for me (I was missing out one of the zero's on the formula near the end)

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,567

    Re: Countifs across a range

    Can you post the formula that you're using with 2 sheets please, Vinnie?

    There are a couple of things that could cause the error you're seeing but without seeing the formula and the layout of each sheet it's practically impossible to figure out the exact reason why it's not working.

  5. #5
    Registered User
    Join Date
    01-11-2019
    Location
    WATFORD
    MS-Off Ver
    16.16.5
    Posts
    3

    Re: Countifs across a range

    Hi Jason.b75

    Sorry for the late response. It was a user error on my side. The CountIf worked how I needed it to.

    Thanks again for you help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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