# SUMIFS Across Rows with Multiple Criteria

#### 0267Kim

##### New Member
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.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Marcelo Branco

##### MrExcel MVP
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

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

1,102,848
Messages
5,489,236
Members
407,682
Latest member
gmb2521

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...