Multi criteria sumifs

[xcelnovice]

I need to match 2 criteria acct# is one the date is the other. However id like for the date to be able to sum all the data in the cells that are a date prior to the reference date. Ex. I have sheet 1 with Jan-Dec in K4-V4 and data below. I have a reference date in sheet 2 3/31/16. I'd like to sum all the data up to what that date is. I tried this...
Sumifs with "<=" & (my cell with the ref date) along with the other criteria but couldn't figure it out? Any insight...sorry couldn't post sample bc I'm on my phone

 

[xcelnovice]

#SUMIFS('Target Est'!K4:U4,'Target Est'!$B$4:$B$2035,BvA!B4,'Target Est'!K2:V2,"<="&BvA!$H$2)


Here was my attempt...K4-U4 is where the data lives id like to sum
B4-B2035 are unque identifiers, B4 is the reference point to compare
K2-V2 is my date reference range & then id like less than the date in H2

does this clarify?

 

[jtakw]

Hi,

The SUMIFS formula requires the ranges to be the same dimension: https://support.office.com/en-us/article/SUMIFS-function-c9e748f5-7ea7-455d-9406-611cebce642b

 

[xcelnovice]

Thank you this was helpful...so I can get it to pull a single month using index match. Any way to add a sum to an index match?

 

[jtakw]

Thank you this was helpful...so I can get it to pull a single month using index match. Any way to add a sum to an index match?

Can't really answer your question without some sample data and your current formula. Please post.

 

[xcelnovice]

Thanks but sum product bailed me out!

 

[jtakw]

Glad you found a solution.