+ Reply to Thread
Results 1 to 9 of 9

Count a range of cells if another range meets criteria

  1. #1
    Registered User
    Join Date
    04-14-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Count a range of cells if another range meets criteria

    Ok, I am trying to do a formula that will count a range (say A1:A6) if another range (B1:B6) has specific text (HI for example) in it. I have tried COUNTIF, COUNTA, COUNTIFS and a couple ofthers and I am stumped.

    Untitled-3.jpg

    Above is the example, this is what I want it to look like:

    Untitled-4.jpg

    Any ideas on what formula will do this???

    Thanks in advance for helping!

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Count a range of cells if another range meets criteria

    Try...

    =COUNTIFS(B1:B6,"HI",A1:A6,">0")
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    04-14-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Count a range of cells if another range meets criteria

    OMG, you are BRILLIANT!!!!!! Now to test it in my actual spreadsheet......

  4. #4
    Registered User
    Join Date
    04-14-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Count a range of cells if another range meets criteria

    It works there too, thank you SOOOOOO much!!!!!

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Count a range of cells if another range meets criteria

    Thanks for the feedback and you are very welcome

  6. #6
    Registered User
    Join Date
    04-14-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Count a range of cells if another range meets criteria

    Hey what about if I want it to the same thing, but pull those numbers from three other tabs? So, tab1 is where we see the result, but the cells the info is being pulled from is on tab2, tab3 and tab4

  7. #7
    Registered User
    Join Date
    04-14-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Count a range of cells if another range meets criteria

    Ok, and now that I'm testing it I need it to cover a not just a column, but from like A1:C6.........all from the other tabs...should I post a pic?

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Count a range of cells if another range meets criteria

    The most efficient method would be to use the Countifs formula on each tab and then on the summary tab...=SUM(Sheet1:Sheet3!B6)

    You can even create one sheet called First and one called Last, put all the sheets inbetween and then...=SUM(First:Last!B6)

    No please don't post a pic, post a workbook instead. It is very hard for us to test the data against a pic and not many people want to re-type all of your data for testing purposes.

  9. #9
    Registered User
    Join Date
    04-14-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Count a range of cells if another range meets criteria

    That sounds good, I was trying to limit the amount of formulas I use (headaches even tho I love doing them) but I like that solution

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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