Subtotals with multiple criteria

I've been asked to do something that's outside of my comfort level, so I've attached an example of what is required.

I would like to find what remains in the budget per department at the end of the month chosen by the user in the List Box in cell Q1. Each department may or may not have more than one line item.

So, if they choose January, column P should show the totals for February and March (March is the year end). But subtotaled by department.

My formula in P2 is getting way too complicated, prone to input error and is not dynamic if a new line item is added. I stopped writing it after the first 3 months. I would have to write a similar formula for each of the departments if I continued in this manner.

There must be a more elegant way to do this, isn't there?

Thanks for any and all advice.

BeauT,

Give this formula a try in cell P2 then copy down:

=SUM(INDIRECT(CONCATENATE(CHAR(64+($S$1+3)),ROW(),":",CHAR(64+14),ROW())))


HTH

retired007geek said:

BeauT,

Give this formula a try in cell P2 then copy down:

=SUM(INDIRECT(CONCATENATE(CHAR(64+($S$1+3)),ROW(),":",CHAR(64+14),ROW())))

View attachment 7227
HTH

Click to expand...

Thanks, retired007geek. Your formula doesn't subtotal by department, but works great otherwise. I'll use it in the main page and then subtotal everything on another sheet.

All is good. You've saved me days of research and frustration.

How about

=SUMPRODUCT(($A$2:$A$13=A2)*((COLUMN($C$1:$N$1)-2)>$S$1)*($C$2:$N$13))

BeauT,

Just use the Subtotal selection on the Data Tab.

View attachment BeauT - Remaining Budget - Formula.xlsx

HTH

Thanks Bob. That puts the subtotals in the original sheet exactly the way I wanted them. Although retired007geek's formula was great, yours saves me another step.

I'm very grateful.