Finding SUM w/ Criteria Over a Large Range

    I have the following data set:


    Columns A, C, E, G, I, K = Month Year (Text)
    Columns B, D, F, H, J, L = # of Hours


    On a separate worksheet, I need to return the sum of the hours if a given month appears in any of the month columns . . . I've tried a few SUMIF and SUMPRODUCT combos, but it's not working?

    Re: Finding SUM w/ Criteria Over a Large Range


    Could you please join your file - without confidential data - and the result you expect ?

    Re: Finding SUM w/ Criteria Over a Large Range


    I've attached a sample spreadsheet as my actual workbook is very large and I'm only managing a portion of it.



    In the attached, I'm trying to solve for the ? field, where for any date put in the "Enter Date" field is looked for across the above data set and the corresponding hours are added together for that month only (and it would change if you change the date).


    Does this help?

    Re: Finding SUM w/ Criteria Over a Large Range


    You can use one of these two formulas:

    Code
    '=SUMIF(A2:A5;B8;B2:B5)+SUMIF(C2:C5;B8;D2:D5)+SUMIF(E2:E5;B8;F2:F5)+SUMIF(G2:G5;B8;H2:H5)'


    Code
    '=SUMPRODUCT((A2:A5=B8)*B2:B5+(C2:C5=B8)*D2:D5+(E2:E5=B8)*F2:F5+(G2:G5=B8)*H2:H5)'


    But i don't like it the way it is. I am almost sure that there is a much easiest way to achieve this. I have to think more about it.

Participate now!

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

Register Yourself Login