+ Reply to Thread
Results 1 to 8 of 8

Help with counting multiple criteria in one column based on another column

  1. #1
    Registered User
    Join Date
    05-17-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2010
    Posts
    14

    Help with counting multiple criteria in one column based on another column

    Hi Everyone,

    I'm very new to this, but am desparate for some help.

    I am trying to count how many times two types of companies are used for each employee. Using this site I found a formula that worked for one type of company, but I couldn't add the second company I wanted it to look up:

    =SUMPRODUCT(--('relocation capture'!$E:$E="Alvarez, Isabel"),--ISNUMBER(SEARCH("Award Escrow","Escrow Network",'relocation capture'!$I:$I)))

    This is what I want:

    Employee Company
    John Award Escrow
    John Escrow Network
    John Peak Escrow

    I have a separate tab that is the summary tab that just gives you totals for each employee. For this Employee the total escrow companies should equal 2.

    Does anyone know how to help me? I'm assuming a lookup type formula would probably be easier so I don't have to customize each search by Agent, but I'm unfamiliar with them and thought i would start with this.

    I thank you in advance for anyone who is able to help.

    Sincerely,

    D

  2. #2
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: Help with counting multiple criteria in one column based on another column

    G'day and welcome to the forum,

    Try this is one way.........

    Please Login or Register  to view this content.
    Have I made you happy ??? If yes, please make me happy by pressing the http://www.excelforum.com/images/buttons/reputation-40b.png Add Reputation button in my post.
    Please don't forget to do the same to other contributors of this forum.

    Thanks
    I don't void confusion, I create it

  3. #3
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Help with counting multiple criteria in one column based on another column

    How to attach a sample Excel workbook:

    1. Below the "Quick Reply" thread frame, click the "Go Advanced" button.
    2. On the edit toolbar, click the "Attachments" button.
    3. In the "File Upload Manager" window, click the "Add Files" button.
    4. In the "Upload Files from your Computer" window, click the "Select Files" button.
    5. In the Windows Explorer window, locate and select your workbook, then click the "Open" button.
    6. In the "Upload Files from your Computer" window, click the "Upload Files" button.
    7. In the "File Upload Manager" window, click the "Done" button (lower right of screen).
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  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: Help with counting multiple criteria in one column based on another column

    Like this...

    =SUMPRODUCT(--('relocation capture'!$E:$E="Alvarez, Isabel"),--ISNUMBER(MATCH('relocation capture'!$I:$I,{"Award Escrow","Escrow Network"},0)))

    You should avoid using entire columns as range references in the SUMPRODUCT function. Use a smaller specific range.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    05-17-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Help with counting multiple criteria in one column based on another column

    Hi Everyone,

    Thank you for your help. I have an additional question. I am wondring if there is an easier way to get what I need. Right now I'm using the formula ratcat gave me. I have a 115 employees so right now I am changing the formula line by line to include each employees name. plus changing it for each criteria column. I was wondering if a vlookup will make it easier so I am not having to change it for each employee. I would like to have 4 or 5 different columns for different criteria that automatically searches for the employees name no matter what order they are in on another tab.

    See attached spreadsheet. I tried to explain on each column what I am looking for. I really hope someone can help. I've been working on this for 5 days.
    Attached Files Attached Files

  6. #6
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Help with counting multiple criteria in one column based on another column

    You coulld save some time by changing your formula in C11 from this:

    Please Login or Register  to view this content.
    to this:

    Please Login or Register  to view this content.
    then just autofill down to complete the list in column C.

    The same approach will work for the formulas in column D and E.

  7. #7
    Registered User
    Join Date
    05-17-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Help with counting multiple criteria in one column based on another column

    I received excellent help with my spreadsheet last month. I was given the formula =SUMIF('relocation capture'!$E:$E,$B11,'relocation capture'!$G:$G) Which worked perfectly. Now I'm trying to do the same thing with a new tab I have added tot he formula so the summary page will show each month's totals, but it isn't working. Can someone tell me what I am doing wrong that the formula doesn't work when I use the January tab? I've tried to write it in, select columns on the tab, but nothing works. You guys are the best and need your help!

    PS right now in the January tab columns on the summary tab it is pulling the relocation tab's info, but I need it to pull the January tab's info. Spreadsheet attached.
    Attached Files Attached Files

  8. #8
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Help with counting multiple criteria in one column based on another column

    All the agent names on your January tab have spaces after them. This is causing the problem for the SUMIF formula.

+ 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