Hi, I'm trying to prepare a reporting tool that will let me see cost and head count sortable by time period.
I can do a countifs or sumifs function easily enough sorting by Division/location/role (columns A-C) if I target the formula to a particular date column. However, I want to be able to change the sum/count range by just changing the date field, and have the sum range pick up and adjust to the date field.
Essentially, how do I do a sumifs function so that the column picked up in the SUM range (columns D-I in example below) changes according to the month I select?
Any help appreciated
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Division[/TD]
[TD]Location[/TD]
[TD]Role[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Production[/TD]
[TD]USA[/TD]
[TD]Dev[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Production[/TD]
[TD]USA[/TD]
[TD]Testing[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Production[/TD]
[TD]EU[/TD]
[TD]Dev[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Production[/TD]
[TD]EU[/TD]
[TD]Testing[/TD]
[TD]1500[/TD]
[TD]1500[/TD]
[TD]1500[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sales and Mkting[/TD]
[TD]USA[/TD]
[TD]Sales[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Sales and Mkting[/TD]
[TD]USA[/TD]
[TD]Mkting[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Management[/TD]
[TD]USA[/TD]
[TD]CEO[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Management[/TD]
[TD]USA[/TD]
[TD]Finance[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[/TR]
</tbody>[/TABLE]
I can do a countifs or sumifs function easily enough sorting by Division/location/role (columns A-C) if I target the formula to a particular date column. However, I want to be able to change the sum/count range by just changing the date field, and have the sum range pick up and adjust to the date field.
Essentially, how do I do a sumifs function so that the column picked up in the SUM range (columns D-I in example below) changes according to the month I select?
Any help appreciated
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Division[/TD]
[TD]Location[/TD]
[TD]Role[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Production[/TD]
[TD]USA[/TD]
[TD]Dev[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Production[/TD]
[TD]USA[/TD]
[TD]Testing[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Production[/TD]
[TD]EU[/TD]
[TD]Dev[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Production[/TD]
[TD]EU[/TD]
[TD]Testing[/TD]
[TD]1500[/TD]
[TD]1500[/TD]
[TD]1500[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sales and Mkting[/TD]
[TD]USA[/TD]
[TD]Sales[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Sales and Mkting[/TD]
[TD]USA[/TD]
[TD]Mkting[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Management[/TD]
[TD]USA[/TD]
[TD]CEO[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Management[/TD]
[TD]USA[/TD]
[TD]Finance[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[/TR]
</tbody>[/TABLE]