+ Reply to Thread
Results 1 to 4 of 4

COUNTA based on match in another column

  1. #1
    Registered User
    Join Date
    08-02-2007
    MS-Off Ver
    2007
    Posts
    25

    COUNTA based on match in another column

    Hi there,

    I'd like to COUNTA in column B if column A equals "Active".

    How would I string together a formula for this?

    Thanks,
    Victoria

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: COUNTA based on match in another column

    If your column B values are text entries then this should work for you

    =COUNTIFS(A:A,"Active",B:B,"*")

    otherwise to count any entry in B when A is active (including numbers) you could use SUMPRODUCT like

    =SUMPRODUCT((A2:A100="Active)*(B2:B100<>""))
    Audere est facere

  3. #3
    Registered User
    Join Date
    08-02-2007
    MS-Off Ver
    2007
    Posts
    25

    Re: COUNTA based on match in another column

    Thanks, this works: =COUNTIFS(A:A,"Active",B:B,"*")


    What does the "*" refer to? How is it used?

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: COUNTA based on match in another column

    In this context "*" is a "wildcard" which denotes any character or any number of characters....so it works to count all column B entries where there is text (and column A is "active").

    Wildcards don't work with numeric values, though, e.g. if column B contains a number like 999 that formula won't count it - if that's a possibility then the SUMPRODUCT version would be preferable

+ 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