How to avoid spill error on Sumif

[87th]

Hi,

I'm trying to create a formula in a table column that sums values based on a date/time range as long as the time difference between any values within the range and the value in the current row is roughly less than 2 minutes. The issue I'm running into is a spill error from said calculation which I would like to find a somewhat non-strenuous means of getting around.

Here is the current formula:
=SUMIF([Update Time],([Update Time]-[@[Update Time]])*1440<=2,[Amount])

Obviously haven't exhaustively completed the calculation, but my issue is really just the spill error. I understand the source of the error and everything, but I'm having a creative block at the moment so open to solutions that may change the methodology completely.

Thank you in advance for any thoughts/help.

 

[hagia_sofia]

Hello, could you please upload a sample of your data so that we could see how it is structured (FILTER + SUM might be a way here)?

 

[Eric W]

Welcome to the MrExcel forum!

You have 2 issues. First, you can't have a Spill formula in a table. I can tell you're using a table by the form of your current formula. Second, when using SUMIF, the way you defined the comparison doesn't work. I tried remediating it with SUMIFS, but it got very long and complicated, so I moved to FILTER as hagia_sofia suggested.

Book1
	A	B	C
1	Update Time	Amount	Near Sum
2	1:00	1	3
3	1:01	2	3
4	1:05	3	16
5	1:10	4	4
6	1:20	5	5
7	1:06	6	16
8	1:06	7	16
9	1:30	8	27
10	1:31	9	27
11	1:32	10	27
Sheet2

Cell Formulas
Range	Formula
C2:C11	C2	=SUM(FILTER([Amount],ABS([Update Time]-[@[Update Time]])*1440<=2))

Since a formula will automatically replicate down all rows in the table, you don't need to use a Spill formula. Let us know if this works for you. Also, you might want to subtract 1 from the answer, since each row will count itself.

 

[87th]

Welcome to the MrExcel forum!

You have 2 issues. First, you can't have a Spill formula in a table. I can tell you're using a table by the form of your current formula. Second, when using SUMIF, the way you defined the comparison doesn't work. I tried remediating it with SUMIFS, but it got very long and complicated, so I moved to FILTER as hagia_sofia suggested.

Book1
	A	B	C
1	Update Time	Amount	Near Sum
2	1:00	1	3
3	1:01	2	3
4	1:05	3	16
5	1:10	4	4
6	1:20	5	5
7	1:06	6	16
8	1:06	7	16
9	1:30	8	27
10	1:31	9	27
11	1:32	10	27
Sheet2

Cell Formulas
Range	Formula
C2:C11	C2	=SUM(FILTER([Amount],ABS([Update Time]-[@[Update Time]])*1440<=2))

Since a formula will automatically replicate down all rows in the table, you don't need to use a Spill formula. Let us know if this works for you. Also, you might want to subtract 1 from the answer, since each row will count itself.

The sum filter approach was exactly what I was looking for, thank you very much!

 

[Eric W]

Glad we could help!