+ Reply to Thread
Results 1 to 19 of 19

Sumifs with multiple criteria for multiple names

  1. #1
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Sumifs with multiple criteria for multiple names

    Hello

    I have been stumped on this formula for a week now, basically what i need is on the projections tab, under the 13 watt light bulbs, to be able to pull the amount of bulbs transferred, but on the final part I need it to select the names column C in the footprints tab, and returns a result using only the names from the A column in drop box tab. The reset of the formula works perfectly except for that part, when i try using just sumifs it will only allow you to select one name at a time, but i want it so that I can add or remove names in the drop bax tab columns which will give different results

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sumifs with multiple criteria for multiple names

    Hi mcayea,

    welcome to the forum.
    Below statement not clear to me.
    but on the final part I need it to select the names column C in the footprints tab, and returns a result using only the names from the A column in drop box tab
    Also it would be helpful if you could show your expected results.. thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    Hey sorry, im now at writting out the issue with excel. This is how i think the formula should look
    =SUMIFS(footprints!I:I,footprints!$G:$G,"<=" &$C12, footprints!$G:$G,">=" &$B12,footprints!$E:$E,"Transfer",footprints!$C:$C,'Drop Brox'!A2:A17)
    However 'Drop Brox'!A2:A17 only returns one name off the list, not all of them. Now I only have one installer in there right now just so i can build the sheet, but i want it to do the whole formula of searching for 13watt lightbulbs if they are between these dates, if they are under the list of installers from "drop box".

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sumifs with multiple criteria for multiple names

    I guess it would be helpful if you show your expected results manually and I will think of the formula to obtain the same.. thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    The expected result for 12/04/2013 - 18/04/2013 on the Projections tab should be 1488, as that is the total for the installer Kevin Surette, however I need it to filter by all the names in column A in the drop box tab i want it to display those results. if for instance Alderic Benson Brian Arsenault were in the footprints tab in column C i want it to return the total sum of all 3 installers. Our export from software doesn't display region and that's how i want to filter them out, if a new installer starts i can add his name to the drop box column and it will return the total number. I can re-upload a file that would have more installer names in the footprints tab,if that makes it easier. so Basically it searches 13watt bulbs between those dates on "projection tab" if that installer name shows up in column A of drop box tab.

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Sumifs with multiple criteria for multiple names

    perhaps
    =SUMPRODUCT(SUMIFS(footprints!I:I,footprints!$G:$G,"<=" &$C12, footprints!$G:$G,">=" &$B12,footprints!$E:$E,"Transfer",footprints!$C:$C,'Drop Brox'!$A$2:$A$17))
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  7. #7
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    Im pretty sure this works perfectly, thank you so much. now i can sleep

  8. #8
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    Thanks for the help on that, I have one more question before everything is done. I want to use a similar formula to figure out what their personal counts are for the projections in row 21. Now that the last formula allowed me to gather information from the "footprints" tab, I need a formula that would go to the "personal counts" tab and look at 13watt bulbs, then look at the list of names in column A, and If there name is in the column A of the "dropbox" tab, then give a total result. I have tried tweaking the one you created earlier, but i cant figure out how you used multiple criteria for the drop box results.

  9. #9
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Sumifs with multiple criteria for multiple names

    perhaps
    =SUMPRODUCT(SUMIFS('Personal Counts'!C:C,'Personal Counts'!A:A,'Drop Brox'!$A$2:$A$17))

  10. #10
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    Hey those actually worked very well, the only issue I'm having and didn't realize i needed was in the =SUMPRODUCT(SUMIFS('Personal Counts'!C:C,'Personal Counts'!$A:$A,'Drop Brox'!$B$2:$B$20)) function, is there a way to do this formula but add a LARGE or LARGEIF to it, so that if there is multiple enteries from the same person on the list in drop box, it only takes their most recent enterie.
    .

  11. #11
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    I need it to pull only the most recent Personal counts enteries for all or the names in their respective columns in the drop brox sheet.

  12. #12
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    Anyone have an idea of which formula to start with?

  13. #13
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sumifs with multiple criteria for multiple names

    I can give it a try if you upload your expected results - manually entered. Thanks.



    Regards,
    DILIPandey
    <click on below * if this helps>

  14. #14
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    Under the sheet projections wolfville sheet the result for cell D21 should equal 864, as in the personal counts sheet the newest entry for kelly gervais from the wolfville drop down box list, instead of adding up all the entries for kelly gervais just give the most recent activity. Cell D21 under Projections Yarmouth should equal 1108, as kevin surette and denis d'entremonts most recent entries are 966+Dennis's 142. I uploaded pronto master 2 which has updated values to match my numbers.
    Attached Files Attached Files

  15. #15
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Sumifs with multiple criteria for multiple names

    assuming the personal counts sheet is sorted in descending date order as in your example, insert a new column A on the personal counts sheet called say 'Is Latest' and in a2 enter
    =COUNTIF(B$1:B1,B2)=0
    and fill down
    then the formula in D21 on wolfsville is
    =SUMPRODUCT(SUMIFS('Personal Counts'!D:D,'Personal Counts'!$B:$B,'Drop Brox'!$G$2:$G$20,'Personal Counts'!$A:$A,TRUE))

  16. #16
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    I thought that formula was working but when i do it for the rest the result isnt the same, for yarmouth it returned a result for 2416 13watt lights, but should be 1108

  17. #17
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    sorry it actually shows up as 966, so it sees one person but not both

  18. #18
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Sumifs with multiple criteria for multiple names

    on the drop brox sheet you have 'denis d'entremont' but it ought to be 'dennis d'entremont' ;-)

  19. #19
    Registered User
    Join Date
    05-02-2013
    Location
    Excel Hell
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Sumifs with multiple criteria for multiple names

    your right, unfortunately the issue with this spread sheet is it uses data bases from different software, so i have to go over it with a fine tooth comb to check spelling of names, thanks a lot

+ 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