Sumif with dynamic range

[AaronO]

Hello,

I am struggling with a formula to create a sumif with a dynamic range.

The data I have is as follows:

[TABLE="width: 1005"]
<tbody>[TR]
[TD]ROW/COLUMN[/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]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Department[/TD]
[TD]Expense[/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]May-18[/TD]
[TD]Jun-18[/TD]
[TD]Jul-18[/TD]
[TD]Aug-18[/TD]
[TD]Sep-18[/TD]
[TD]Oct-18[/TD]
[TD]Nov-18[/TD]
[TD]Dec-18[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Finance[/TD]
[TD]Phone[/TD]
[TD="align: right"]199[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Operations[/TD]
[TD]Recruitment[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]194[/TD]
[TD="align: right"]191[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]178[/TD]
[TD="align: right"]162[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Finance[/TD]
[TD]IT set up[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]94[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Management[/TD]
[TD]etc[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]86[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Admin[/TD]
[TD]etc[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]177[/TD]
[TD="align: right"]159[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Operations[/TD]
[TD]etc[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]182[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]43[/TD]
[/TR]
</tbody>[/TABLE]

What I am after is to be able to type a month in a cell (say in cell Summary!A2) and a department in a cell (say Summary!A3), and then have two formulas. One summing up the numbers in the relative month column whereby the row matches to my department input. And one that provides a YTD amount for the department.

For example, if I typed Apr-18 and Finance into the two specified cells, I would want the first formula to sum up F2 & F4, and the second formula to sum up C2:F2 & C4:F4.

I have tried using Offsets and Match but just cannot get my formulae to work.

Will appreciate any help that can be provided.

Thank you,

Aaron

 

[dreid1011]

Here are the formulas I came up with. You will have to add sheet names as you did not make it clear which sheet the formulas are going to be in and what the sheet name is where the data is located.

Formula 1:
=SUMPRODUCT((TEXT(C1:N1,"mmmm")=A11)*(A2:A7=A12)*C2:N7)

C1:N1 = Date Headers
A2:A7 = Departments
C2:N7 = Values
A11 and A12 would be your Summary!A2 and Summary!A3

Formula 2:
=SUMPRODUCT((MONTH(C1:N1)<=MONTH(DATEVALUE(A11&"1")))*(A2:A7=A12)*C2:N7)

C1:N1 = Date Headers
A2:A7 = Departments
C2:N7 = Values
A11 and A12 would be your Summary!A2 and Summary!A3