Sumifs statement embedded with a match closest date as one of the criteria

[zach9208]

I need a formula that can be used with a sumifs statement that can match one date from one list to the closest date on another.

For example, trade date was Dec 5th, I need this to be matched with the date labeled Dec 1st.

The problem I am having is that I am having trouble entering the criteria cell within my sumifs statement to find the closest date.

I was using the formula below embedded in my sumifs formula as the criteria but it kept spitting out 0 when it should have spit out the correct value.

My formula looks like this
=sumifs($D:$D,$A:$A,$B$4,$C:$C,$B$5,$E:$E,(INDEX(A1:A20,MATCH(MIN(ABS(A1:A20-D1)),ABS(A1:A20-D1),0)))

$E$E is the lookup range for dates, while the index function is the criteria.

Any ideas?

 

[DILIPandey]

Hi zach9208,

would be helpful if you post a sample data. Thanks.


Regards,
DILIPandey

 

[zach9208]

Overview #2 Tab
Column A Column B

4 RSE
5 Client Test Client
6 BA ID 100
7 Trade Date 9/18/2015



14 C30 Non HB (C14 should be showing matching marginal revenue)

Rev Data Tab
Column A B C D E F
ClientName BusnAgrmtId EffectiveDate ExpirationDate Product Marginal Revenue
Test Client 100 12/1/2015 1/1/2099 C30 Non-HB 0.325427
Test Client 100 11/1/2015 11/30/2015 C30 Non-HB 0.349347
Test Client 100 10/1/2015 10/31/2015 C30 Non-HB 0.345897
Test Client 100 9/1/2015 9/30/2015 C30 Non-HB 0.258339
Test Client 100 8/1/2015 8/31/2015 C30 Non-HB 0.257488
Test Client 100 7/1/2015 7/31/2015 C30 Non-HB 0.341798
Test Client 100 6/11/2015 6/30/2015 C30 Non-HB
Test Client 100 6/1/2015 6/10/2015 C30 Non-HB
Test Client 100 5/1/2015 5/31/2015 C30 Non-HB
Test Client 100 4/1/2015 4/30/2015 C30 Non-HB
Test Client 100 3/1/2015 3/31/2015 C30 Non-HB
Test Client 100 2/1/2015 2/28/2015 C30 Non-HB


My current formula is this =SUMIFS('Rev Data'!$F:$F,'Rev Data'!$B:$B,'Overview #2'!$C$6,'Rev Data'!$E:$E,'Overview #2'!$B14,'Rev Data'!$C:$C,((INDEX('Rev Data'!C:C,MATCH('Overview #2'!C7,'Rev Data'!D:D,'Rev Data'!C:C))))*100)


It should be returning 0.258339 as the result since it is closest to the trade date of 9/18 and meets all of the sumifs criteria.

 

[DILIPandey]

Hi Zach,

Tab overview is not clear to me, can you put boarders in that sheet and paste like below:-

[TABLE="width: 188"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]RSE[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Test[/TD]
[TD]Client[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]ID[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Trade[/TD]
[TD]Date[/TD]
[TD="align: right"]9/18/2015[/TD]
[/TR]
</tbody>[/TABLE]


Regards,
DILIPandey

 

[Aladin Akyurek]

[...]

My current formula is this =SUMIFS('Rev Data'!$F:$F,'Rev Data'!$B:$B,'Overview #2'!$C$6,'Rev Data'!$E:$E,'Overview #2'!$B14,'Rev Data'!$C:$C,((INDEX('Rev Data'!C:C,MATCH('Overview #2'!C7,'Rev Data'!D:D,'Rev Data'!C:C))))*100)


It should be returning 0.258339 as the result since it is closest to the trade date of 9/18 and meets all of the sumifs criteria.

What do you have exactly in the following cells?

'Overview #2'!$C$6
'Overview #2'!$B14
'Overview #2'!C7

 

[zach9208]

Thanks for the response back. I decided to take a different route. I used a couple of queries to slice and dice more to avoid the need for such a formula. Thanks again for the help.

If either of you have a strong background in VBA, check out my post on creating a loop that duplicates an entire workbook after changing the value of a cell until a workbook is created for each individual client. Thanks again.

 

[Aladin Akyurek]

Thanks for the response back. I decided to take a different route. I used a couple of queries to slice and dice more to avoid the need for such a formula. Thanks again for the help.

If either of you have a strong background in VBA, check out my post on creating a loop that duplicates an entire workbook after changing the value of a cell until a workbook is created for each individual client. Thanks again.

I'd have liked the answer to the question though for SUMIFS admits a calculated criterion.

 

[zach9208]

Unfortunately I am out of work today and for several days, so I do not have the workbook with me. I have used sumifs before to quickly filter out results by matching 4+ criterion. It works perfectly. The fact is that my formula was looking at data that was returning more than 1 result and I needed to match the cell with a column that was the closest match to 0. I do not think it is even possible to combine a sumifs with an index match in an array.

 

[Aladin Akyurek]

Unfortunately I am out of work today and for several days, so I do not have the workbook with me. I have used sumifs before to quickly filter out results by matching 4+ criterion. It works perfectly. The fact is that my formula was looking at data that was returning more than 1 result and I needed to match the cell with a column that was the closest match to 0. I do not think it is even possible to combine a sumifs with an index match in an array.

I do think that SUMIFS can have a calculated criterion which is a set...