+ Reply to Thread
Results 1 to 5 of 5

COUNTIFS combining text and values

  1. #1
    Registered User
    Join Date
    03-08-2015
    Location
    UK
    MS-Off Ver
    2013
    Posts
    33

    Question COUNTIFS combining text and values

    Question that I am struggling with somewhat.

    I have a list of account values and then manager names next to them. What I would like to do is count how many accounts two particular managers have that are less than a certain number. E.g. How many accounts in total that both Terry and James (which to me is an OR function) manage are less than 50,000 in value. I've tried to use various permutations of COUNTIFS but to no avail. In the above example the answer is 3 but I can't manage to figure it out.

    Manager Acc Value
    Terry 20,000
    James 1,000
    John 80,000
    Terry 100,000
    James 90,000
    John 2,000
    Terry 3,000
    James 55,000
    John 6,000

    Many thanks in advance
    Last edited by jcswaby; 03-16-2015 at 10:02 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,726

    Re: COUNTIFS combining text and values

    You could do it like this:

    =COUNTIFS(A:A,"Terry",B:B,"<50000")+COUNTIFS(A:A,"James",B:B,"<50000")

    Hope this helps.

    Pete

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: COUNTIFS combining text and values

    Or

    =SUM(COUNTIFS(A:A,{"Terry","James"},B:B,"<50000"))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    03-08-2015
    Location
    UK
    MS-Off Ver
    2013
    Posts
    33

    Re: COUNTIFS combining text and values

    Thanks guys

  5. #5
    Registered User
    Join Date
    03-08-2015
    Location
    UK
    MS-Off Ver
    2013
    Posts
    33

    Re: COUNTIFS combining text and values

    Just thought, how would you find the smallest/largest number based on the same criteria?

+ 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. [SOLVED] Combining COUNTIFS and ISNUMBER
    By OverKnight in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-05-2015, 12:55 AM
  2. Combining Indirect with Countifs
    By arthurphil in forum Excel General
    Replies: 7
    Last Post: 01-27-2015, 08:13 AM
  3. [SOLVED] Combining CountIfS Formulas
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-21-2013, 09:25 AM
  4. Combining COUNTIFS and OR function
    By tatyanamarie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-12-2013, 08:51 AM
  5. [SOLVED] Combining two COUNTIFS
    By fletch8701 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-19-2012, 11:07 PM

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