SUMIFS Across Rows with Multiple Criteria

[0267Kim]

Hi Group!

I've got a question on using SUMIFS to pull data horizontally with multiple criteria. I have a table of data in which the column headers are month end dates, and each row corresponds to a unique account with monthly data amounts.

Example

	A	B	C	D	E
1		1/31	2/28	3/31	4/30
2	103	2	3	1	4
3	101	3	2	1	1
4	102	4	2	0	1
5	104	0	0	2	0

<tbody>
</tbody>

I want to write a SUMIFS statement which will sum columns B to E where column A equals a certain account, and where row 1 (with the month end dates) is less than or equal to the current month end on another sheet. So, if the month end date were 3/31 and we wanted the year to date total for account 101, the result would be 6.

I have tried to SUMIFS(B:E, B1:E1, <= 3/31, A1:A5, "101") I'm getting a value error which I cant seem to get around.

Any help would be greatly appreciated.

Thank you.

 

[Marcelo Branco]

Welcome to Mr Excel Forum

Dates as mm/dd/yyyy

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​		01/31/2019​	02/28/2019​	03/31/2019​	04/30/2019​		Account​	Ref Date​	Result​
2​	103​	2​	3​	1​	4​		101​	03/31/2019​	6​
3​	101​	3​	2​	1​	1​
4​	102​	4​	2​	0​	1​
5​	104​	0​	0​	2​	0​
6​

Criteria in G2:H2

Formula in I2
=SUMIF(B$1:E$1,"<="&H2,INDEX(B$2:E$5,MATCH(G2,A$2:A$5,0),0))

Hope this helps

M.

 

[0267Kim]

Thanks Marcelo for the prompt response! That worked great. Thank you!