Define sum range based on column header

[Cleland]

Hi there,

I have a sheet (WorkingDays) that has working days for employees by month. Example attached.

I have another sheet (Summary) where I have the current month value e.g. Mar

On the summary sheet I want to display the cumulative working days for the year to date by person, based on the current month value. I need the sum range to be dynamic and expand as the month current month changes.

Does anyone have any advice on how to achieve this?

I tried to use INDIRECT and MATCH to get the col and row values for the sum, but the column value needs to be a letter and match returns a number.

Any thoughts appreciated.

Thanks,

Tom

[Rioran]

Hello, Cleland.

If I get it right... Take a look at the attachment.

You may choose month from drop down list by clicking on month cell and then by clicking on small button that will appear to the right.

Please Login or Register  to view this content.

[AlKey]

Or this

=SUMPRODUCT((WorkingDays!$A$2:$A$4=A4)*(MONTH(1&WorkingDays!$B$1:$M$1)<=MONTH(1&B$1))*(WorkingDays!$B$2:$M$4))

[FDibbins]

If your names will always be in the same order, use this...
=SUMIF(WorkingDays!$B$1:$M$1,Summary!$B$1,WorkingDays!B2:M2)

If the names wont always be inthe same order, use this...
=SUMPRODUCT((WorkingDays!$B$1:$M$1=Summary!$B$1)*(WorkingDays!$A$2:$A$4=Summary!A4)*WorkingDays!$B$2:$M$4)

Both regular formulas, copied down.

I like Rioran's idea of using a dropdown for the date, keeps it clean

[Rioran]

Hello, Ford!

Thank you, I appreciate your opinion =) And I also like your Idea of using SUMPRODUCT cheaty formula.

[Cleland]

Very helpful. Thanks for the great ideas.

Tom

[FDibbins]

Happy to help

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.