+ Reply to Thread
Results 1 to 4 of 4

Counting Multiple Values in a column based on critera in seperate column

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    Aledo, Texas
    MS-Off Ver
    Excel 2010
    Posts
    10

    Question Counting Multiple Values in a column based on critera in seperate column

    Hello!

    I have a list of 100+ values that will be listed in column A. I would like to count how many times each value occurs in Column A given that Column F does not contain a value.

    I have previously been using the formula =COUNTIFS(A:A, "VALUE NAME", F:F, ""), and that does the job, however, to type that formula for the 100+ values individually is very time consuming.

    Additionally, the columns I am counting from are on sheet1 and the list of values that will be used in column A are on a separate worksheet (sheet2) in the same workbook.

    I am hoping that there is a simpler way to do it, maybe a macro?

    I have attached the workbook for your viewing pleasure, there are other issues I am also working through, so just ignore anything that doesn't seem right.

    Many thanks in advance!
    Attached Files Attached Files
    Last edited by ERoberts; 04-11-2013 at 01:08 PM.

  2. #2
    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,233

    Re: Counting Multiple Values in a column based on critera in seperate column

    extract the unique values onto the 2nd sheet using

    In Cell Sheet 2 A2 put

    =INDEX(sheet1!$A$2:$A$100, MATCH(0, COUNTIF($A1:A$1, sheet1!$A$2:$A$100), 0))
    and enter as an array formula using
    control + shift + enter so you get {} around the formula

    and then you can use
    in sheet 2 B2

    =Countif(=COUNTIF(sheet1!A2:A100,sheet2!A2)

    dont understand the example sheet you posted as on sheet no data in column A
    and the cells names in well names appear unique ??

    you could also use a pivot table if needed
    Last edited by etaf; 04-11-2013 at 12:30 PM.

  3. #3
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Counting Multiple Values in a column based on critera in seperate column

    ERoberts,

    Welcome to the forum!
    Why not just reference the cell containing the "VALUE NAME" in the formula instead of typing it out? So instead of:
    Please Login or Register  to view this content.

    Use this:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    04-09-2013
    Location
    Aledo, Texas
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Counting Multiple Values in a column based on critera in seperate column

    Thanks Etaf, I can see how that would work, however to make sure that rows where column F has value, the formula would have to be slightly altered.

    Tigeravatar, I tried it and it works like a gem.

    Thank ya'll so much!

+ 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