SUMIFS Across Rows with Multiple Criteria

0267Kim

New Member
Joined
Aug 20, 2019
Messages
8
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.
 

Some videos you may like

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
Joined
Aug 23, 2010
Messages
16,371
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top