+ Reply to Thread
Results 1 to 8 of 8

SUMIF from a list of criteria

  1. 06-14-2013, 10:33 AM #1
    ncurran217
    ncurran217 is offline
    Forum Contributor
    Join Date
    11-28-2011
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    129

    SUMIF from a list of criteria

    I am trying to make a chart, where it counts the number of reps for that day and how many calls they took. I have attached a sheet with sample data. In columns G - J, I have a list of dates and under the date is the list of reps that were there for that day. In columns L - O, I have the call detail. I column A is the date, column B is the # of Reps for that date, and column C is the amount of calls that group on that date took. Would index & match work inside a sumif? I am not sure how I would configure a formula of sorts. Any help would be great. Let me know if there are questions on what I am looking for.
    Attached Files Attached Files
    Last edited by ncurran217; 06-14-2013 at 03:39 PM.
    Reply With QuoteRegister To Reply
  2. 06-14-2013, 10:44 AM #2
    dluhut
    dluhut is offline
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: SUMIF from a list of criteria

    Would you be able to shed some light of how did you get 122 or 98 for the # of Calls?

    Also, is your data going to be display like as how you've shown to us? i.e. The Date across in one sheet for column G-J (I assume) and another sheet for column L - O?
    Reply With QuoteRegister To Reply
  3. 06-14-2013, 10:48 AM #3
    ncurran217
    ncurran217 is offline
    Forum Contributor
    Join Date
    11-28-2011
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    129

    Re: SUMIF from a list of criteria

    Yes, they are eventually going to be on separate sheets, but named ranges as well. I manually added the calls together to get the total, so it would be known what the answer should be. In columns L, I looked at what the date was and then within that date range, say 6/3/13, I then found the names of that date which is in column I, and then added those calls up for those people for that date in column O. Hopefully this explains it a little better.
    Reply With QuoteRegister To Reply
  4. 06-14-2013, 10:52 AM #4
    dluhut
    dluhut is offline
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: SUMIF from a list of criteria

    Great to know that at least it'll be in different sheets with name range too.

    As for 122 or 98, I can't find those numbers may it be based on each rep on each date or the total number of call per date.

    i.e. I have a total of 451 calls on 6/3/13 and 275 total calls on 6/4/13.

    I didn't see any 122 or 98 on any reps on any dates. That's why I'm confused on how you get the the numbers.
    Reply With QuoteRegister To Reply
  5. 06-14-2013, 10:56 AM #5
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMIF from a list of criteria

    I get 216 for 3rd June using this formula in E3 copied down

    =SUMPRODUCT(SUMIF(N:N,INDEX(G$3:J$20,0,MATCH(A3,G$2:J$2,0)),O:O))

    Assumes that names in columns G:J will go no further than row 20, adjust as required
    Audere est facere
    Reply With QuoteRegister To Reply
  6. 06-14-2013, 11:01 AM #6
    ncurran217
    ncurran217 is offline
    Forum Contributor
    Join Date
    11-28-2011
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    129

    Re: SUMIF from a list of criteria

    That number seems to be a total for that day, not that group of people on that day.

    Edit:

    Also, I am sorry, I had a couple errors on the what the totals should be:

    E5 = 121
    E6 = 95

    Edit:

    The number you got with the formula is the total for those people, not separated out for the day. And then in C4, since the names for that day are two less that is why the number is different.
    Last edited by ncurran217; 06-14-2013 at 11:05 AM.
    Reply With QuoteRegister To Reply
  7. 06-14-2013, 11:09 AM #7
    dluhut
    dluhut is offline
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: SUMIF from a list of criteria

    Quote Originally Posted by ncurran217 View Post
    That number seems to be a total for that day, not that group of people on that day.
    How did you determine "that group of people"

    Honestly, I can't find how to get 121 or 95
    Reply With QuoteRegister To Reply
  8. 06-14-2013, 11:10 AM #8
    ncurran217
    ncurran217 is offline
    Forum Contributor
    Join Date
    11-28-2011
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    129

    Re: SUMIF from a list of criteria

    I got it. I changed the sumif to sumifs and added the date range and the date to look at:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1