Horizontal Sumif

[lorber123]

I am trying to calculate Year to Date totals. I have provided an example of two people in lines 3 and 4. Adam's full year is from Month 1 to Month 12. Column C shows that he started in Month 1. Ben started in month 5 and therefore his full year is from month 5 to month 4 of the next year.

Columns 9 and 10 are the year to date calculations. Column P9 and P10 is where I need a formula created that I can copy to the left and to the right. I need something dynamic so that if I have 100 people who all start in different months, I can calculate how many units each one produced within their specific year. Once the year ends for an individual the start of the next year will be month 1 for Adam and month 5 for Ben.

[karedog]

Hi,

Click cell D8 and input this array formula :

Please Login or Register  to view this content.

and ended by hold and press Ctrl-Shift-Enter at once

Then press Ctrl-C, block D9:AA10 and press Ctrl-V

Now you may delete C8

[lorber123]

This looks great. Is it possible to add a criteria to look up the person's name. That way, if the Year to Date section is filtered, it will look up the person's name and the month started from the above table.

[karedog]

This looks great. Is it possible to add a criteria to look up the person's name. That way, if the Year to Date section is filtered, it will look up the person's name and the month started from the above table.

You don't need to change the formulas at all, just add a table at B8:B10 and it's done.