Formula to Add Across Large Range based on Multiple Criteria

    Hello,


    I'm attaching sample data to help describe what I am looking for. On the attached, I'm trying to solve for H14. The formula should look in the above data and match D14 to column A and D12 to any occurrence in the Month columns (D12 could change to be any month) and then add the corresponding hours for matches. In other words, I want to know how many hours are allotted to Mary Davis for the month selected in D12, based on the 6 months of data above?


    I tried my hand at a SumProduct formula but it delivered a #NAME error.


    Thank you for your help,
    Corcelle

    Re: Formula to Add Across Large Range based on Multiple Criteria


    Try:


    =SUMPRODUCT(($A$2:$A$9=$D14)*(TEXT($D$2:$N$9,"MMMM YYYY")=TEXT($D$12,"MMMM YYYY")),$E$2:$O$9)


    copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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

Register Yourself Login