Thread: SUMIFS Across Rows with Multiple Criteria Thanks: 0 Likes: 0

1. SUMIFS Across Rows with Multiple Criteria

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

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.

2. Re: SUMIFS Across Rows with Multiple Criteria

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.

3. Re: SUMIFS Across Rows with Multiple Criteria

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