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

[TABLE="width: 288"]
<tbody>[TR]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] "][/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] "]A
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] "]B
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] "]C
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] "]D
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] "]E
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/31
[/TD]
[TD="bgcolor: transparent, align: right"]2/28
[/TD]
[TD="bgcolor: transparent, align: right"]3/31
[/TD]
[TD="bgcolor: transparent, align: right"]4/30
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]103
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]101
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]102
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]104
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]


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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

01/31/2019​

[/td][td]

02/28/2019​

[/td][td]

03/31/2019​

[/td][td]

04/30/2019​

[/td][td][/td][td]

Account​

[/td][td]

Ref Date​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

103​

[/td][td]

2​

[/td][td]

3​

[/td][td]

1​

[/td][td]

4​

[/td][td][/td][td]

101​

[/td][td]

03/31/2019​

[/td][td]

6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

101​

[/td][td]

3​

[/td][td]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

102​

[/td][td]

4​

[/td][td]

2​

[/td][td]

0​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

104​

[/td][td]

0​

[/td][td]

0​

[/td][td]

2​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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!