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:

ROW/COLUMN	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Department	Expense	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
2	Finance	Phone	199	90	10	7	47	50	112	52	190	89	11	110
3	Operations	Recruitment	112	161	20	142	86	194	191	13	178	162	170	27
4	Finance	IT set up	187	13	116	161	86	60	155	93	170	161	43	94
5	Management	etc	133	55	151	83	57	74	151	57	36	96	69	86
6	Admin	etc	87	2	36	26	124	70	177	159	38	53	101	123
7	Operations	etc	51	160	56	72	3	182	153	59	7	89	5	43

<tbody>
</tbody>

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