+ Reply to Thread
Results 1 to 7 of 7

Need to count items in column that match multiple data items

  1. #1
    Registered User
    Join Date
    02-10-2014
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    10

    Need to count items in column that match multiple data items

    I need to count the total number of times 4 different values appear in a column. This formula works for one value: =COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1) Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.

  2. #2
    Registered User
    Join Date
    03-30-2013
    Location
    Romania
    MS-Off Ver
    Excel 2016
    Posts
    25

    Re: Need to count items in column that match multiple data items

    You can put the countifs into a AND formula, and the formula returns a TRUE/FALSE response.

    Or eventually to separate the three critearias and you can input an IF formula, like I did, to get any message you want, like in my example, down bellow.

    Need to count items in column that match multiple data items.xlsx

    Hope it helps!
    Last edited by saintdevil8; 03-27-2014 at 09:17 AM.

  3. #3
    Registered User
    Join Date
    02-10-2014
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Need to count items in column that match multiple data items

    Thanks for the quick reply. I had tried using the And function but without success. I am guessing that is because I did not configure the formula correctle. If you can alter the formula I entered to show how to configure with the AND function I would greatly appreciate it. Again thank you for your consideration

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need to count items in column that match multiple data items

    Try this...

    =SUMPRODUCT(COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1:$K$1))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    02-10-2014
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Need to count items in column that match multiple data items

    Thanks saintdevil8 but that isn't what I am looking for. I may not be explaining it so let me try again. I am looking to evaluate the data in the Source file where the Resolved Date is equal to value in column B5 and the value in cell L3 then the values in cells H1,I1,J1 and K1. My expected results would be to increment the count by 1 for each value match of H1,I1,J1 and K1. So my result should be numerical. Again I really appreciate your consideration.

  6. #6
    Registered User
    Join Date
    02-10-2014
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Need to count items in column that match multiple data items

    Tony, thank you that worked. I haven't used the SUMPRODUCT function before but will now consider it for future applications. Again thank you that was awesome!!!

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need to count items in column that match multiple data items

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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. Count of Items that are Greater than the Average of those Items
    By bbg22 in forum Access Tables & Databases
    Replies: 1
    Last Post: 03-19-2014, 07:07 AM
  2. Replies: 3
    Last Post: 02-07-2014, 03:01 AM
  3. Select Multiple Items in a List and then Print Those Items
    By dcdoc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-26-2013, 01:16 PM
  4. [SOLVED] VBA copies rows based on specific items in a column but need to rename items created
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-06-2013, 11:38 PM
  5. Replies: 3
    Last Post: 01-21-2010, 06:58 AM

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