SumIf cell is equal to any in a list

    Hello all,


    I have had no luck with internet searches nor racking my brain for a solution. The basic problem is this, I have a large range of data 2000+ cells and I need to Add only the hours for a division within a department.
    The division is given with a list of names. To be more clear here is an example


    Name Hours
    Doe 2
    Smith 3
    Murphy 3
    Roberts 8
    Mollard 4


    Names belonging to Division A_


    Doe
    Mollard


    Division A Total hours:


    6




    Usually i would just do a series of If statements but there is between 30 and 55 names in one division and this would be painstakingly tedious (I have to do this same thing for 13 divisions...)
    Ideally I would just like to Specify my Sum Range, Specify the Criteria Range, Specify the Criteria List. Exactly like a Sumif...but the Criteria can be anyone in a list. I thought of some combination of Vlookup or match might work but have had no luck so far.


    Thanks for the help,


    Brewface

    Re: SumIf cell is equal to any in a list


    use SUMPRODUCT...see the helpfile for syntax etc.....there are numerous threads for the same on this forum

    Re: SumIf cell is equal to any in a list


    I had thought of using sumproduct but my problem was that you could again only test one condition at a time not an entire list of conditions against one cell, unless I am missing something. It looks like I will just have make a calculation column and then drag and drop for each condition and make a matrix then sum that, simple enough. Was hoping for a one stop formula to test all conditions, but i can make this work with no issues I think. Thanks!

    Re: SumIf cell is equal to any in a list


    Hi Brewface
    Does one of the columns in your data contain division and/or department?

    Re: SumIf cell is equal to any in a list


    Assuming your data is in A1:B5 and the two division names are in E5:E6:


    =SUMPRODUCT(SUMIF(A1:A5,E5:E6,B1:B5))


    will do it.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: SumIf cell is equal to any in a list


    That is genius!!
    Would you mind explaining how it is that works?? I understand the syntax of both but didn't know you could combine them in such a way...

    Re: SumIf cell is equal to any in a list


    By passing an array/range as the criteria part of SUMIF you get an array of values back (effectively a series of SUMIF formulas using each criterion in turn) and the SUMPRODUCT formula then adds these up. You can also use SUM rather than SUMPRODUCT but then you have to array enter the formula with Ctrl+Shift+Enter.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: SumIf cell is equal to any in a list



    you just use sumifs against sumif. sumifs has the option to add lots of Critia,range and most important thing is that you can easily understand how it works.
    thanks

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login