SUMIFS with multiple criteria

[JohnZ1156]

I'm trying to write a formula that has multiple criteria for the SUMIFS Function.

Here is my formula now:
SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1+2,Table2[CTG],"Interest",Table2[CLR],"R")

This works if the CLR column = R
I'd like it to work if the CLR column equaled either R or C, but when I tried this:

SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1+2,Table2[CTG],"Interest",Table2[CLR],"R",Table2[CLR],"C")
It doesn't work.

Do I need maybe an OR operator, like column CLR equals either R or C?

 

[JohnZ1156]

Oh...then that is an easier way to make the R OR C selection. Instead, the criteria would be [CLR]<>""...and that gets you either "R" or "C", while excluding all blank [CLR] records.

I tried your suggestion of changing all of the Table2[CLR],"R" to Table2[CLR]<>"", but it didn't work.
This "sort of" works. Now it includes both "R" and "C", but it doesn't exclude the blanks.
I also got rid of the double SUMIFS at the end. It was doubling the interest for the specific date.

=IF(SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1,Table2[CTG],"Interest")=0,"YTD = "&TEXT(SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&DATE(YEAR(H1),1,1),Table2[DATE],"<="&H1,Table2[CTG],"Interest"),"0.00"),TEXT(SUMIFS(Table2[DATE],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1,Table2[CTG],"Interest",Table2[CLR],"<> "),"m/d")&" - "&SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1+2,Table2[CTG],"Interest",Table2[CLR],"<> "))

 

[KRice]

Have you tried the formula I posted?

=LET(fary,(Table2[DATE]<=H1)*(Table2[CTG]="Interest")*(Table2[CLR]<>""), fmi,FILTER(Table2,fary*(Table2[DATE]>=(H1-2)),SEQUENCE(1,COLUMNS(Table2),0,0)), smi,SUM(INDEX(fmi,,2)),
IF(smi=0, "YTD = "&TEXT(SUM(INDEX(FILTER(Table2,fary*(YEAR(Table2[DATE])=YEAR(H1)),0),,2)),"0.00"),TEXTJOIN(" - ",TRUE,TEXT(INDEX(fmi,,1),"m/d"),TEXT(smi,"0.00"))))

 

[JohnZ1156]

Have you tried the formula I posted?

=LET(fary,(Table2[DATE]<=H1)*(Table2[CTG]="Interest")*(Table2[CLR]<>""), fmi,FILTER(Table2,fary*(Table2[DATE]>=(H1-2)),SEQUENCE(1,COLUMNS(Table2),0,0)), smi,SUM(INDEX(fmi,,2)),
IF(smi=0, "YTD = "&TEXT(SUM(INDEX(FILTER(Table2,fary*(YEAR(Table2[DATE])=YEAR(H1)),0),,2)),"0.00"),TEXTJOIN(" - ",TRUE,TEXT(INDEX(fmi,,1),"m/d"),TEXT(smi,"0.00"))))

Yes, it returned YTD = 135989.00 in the cell.
I almost had a heart attack. $135,989.00 interest for the year? Holy crap !!!

 

[KRice]

Well that number (135989) appears to be suspiciously close to three times the numeric value of dates in early 2024, so I suspect the Date column is being summed rather than the Deposit (Interest) column. Your table must be arranged differently than shown. Can you review the column order and let me know what they are. The formula assumes Date, Deposit, CTG, and CLR, in that order.

 

[JohnZ1156]

Well that number (135989) appears to be suspiciously close to three times the numeric value of dates in early 2024, so I suspect the Date column is being summed rather than the Deposit (Interest) column. Your table must be arranged differently than shown. Can you review the column order and let me know what they are. The formula assumes Date, Deposit, CTG, and CLR, in that order.

Here are all the columns in the table. There are 7153 rows, (not that that matters).
A B C D E F G H I J K
Record - Date - Num - Withdrawal - Deposit - DED - CLR - Balance - CTG - Description - Memo

 

[KRice]

Okay, I'm assuming that all of these columns are part of Table2? When the FILTER function extracts some of the records from Table2, we need to ensure that the correct columns are being used. In the formula I posted, this is done with the INDEX function, and columns 1 and 2 are specified in different places, depending on whether we want the Date or Deposit. But based on what you've just described, those two columns will actually have column indices of 2 and 5.

So rather than (see red highlight)...
=LET(fary,(Table2[DATE]<=H1)*(Table2[CTG]="Interest")*(Table2[CLR]<>""), fmi,FILTER(Table2,fary*(Table2[DATE]>=(H1-2)),COLUMN(Table2)^0-1), smi,SUM(INDEX(fmi,,2)),
IF(smi=0, "YTD = "&TEXT(SUM(INDEX(FILTER(Table2,fary*(YEAR(Table2[DATE])=YEAR(H1)),0),,2)),"0.00"),TEXTJOIN(" - ",TRUE,TEXT(INDEX(fmi,,1),"m/d"),TEXT(smi,"0.00"))))
...the formula should be:

=LET(fary,(Table2[DATE]<=H1)*(Table2[CTG]="Interest")*(Table2[CLR]<>""), fmi,FILTER(Table2,fary*(Table2[DATE]>=(H1-2)),COLUMN(Table2)^0-1), smi,SUM(INDEX(fmi,,5)),
IF(smi=0, "YTD = "&TEXT(SUM(INDEX(FILTER(Table2,fary*(YEAR(Table2[DATE])=YEAR(H1)),0),,5)),"0.00"),TEXTJOIN(" - ",TRUE,TEXT(INDEX(fmi,,2),"m/d"),TEXT(smi,"0.00"))))

By the way, you'll see that column 2 in my example is the Deposit column; whereas in reality, your Date column is the 2nd column. So the large "interest" value you obtained was indeed adding dates, probably three of them based on the size of the number.

 

[JohnZ1156]

Okay, I'm assuming that all of these columns are part of Table2? When the FILTER function extracts some of the records from Table2, we need to ensure that the correct columns are being used. In the formula I posted, this is done with the INDEX function, and columns 1 and 2 are specified in different places, depending on whether we want the Date or Deposit. But based on what you've just described, those two columns will actually have column indices of 2 and 5.

So rather than (see red highlight)...
=LET(fary,(Table2[DATE]<=H1)*(Table2[CTG]="Interest")*(Table2[CLR]<>""), fmi,FILTER(Table2,fary*(Table2[DATE]>=(H1-2)),COLUMN(Table2)^0-1), smi,SUM(INDEX(fmi,,2)),
IF(smi=0, "YTD = "&TEXT(SUM(INDEX(FILTER(Table2,fary*(YEAR(Table2[DATE])=YEAR(H1)),0),,2)),"0.00"),TEXTJOIN(" - ",TRUE,TEXT(INDEX(fmi,,1),"m/d"),TEXT(smi,"0.00"))))
...the formula should be:

=LET(fary,(Table2[DATE]<=H1)*(Table2[CTG]="Interest")*(Table2[CLR]<>""), fmi,FILTER(Table2,fary*(Table2[DATE]>=(H1-2)),COLUMN(Table2)^0-1), smi,SUM(INDEX(fmi,,5)),
IF(smi=0, "YTD = "&TEXT(SUM(INDEX(FILTER(Table2,fary*(YEAR(Table2[DATE])=YEAR(H1)),0),,5)),"0.00"),TEXTJOIN(" - ",TRUE,TEXT(INDEX(fmi,,2),"m/d"),TEXT(smi,"0.00"))))

By the way, you'll see that column 2 in my example is the Deposit column; whereas in reality, your Date column is the 2nd column. So the large "interest" value you obtained was indeed adding dates, probably three of them based on the size of the number.

Hi KRice,
As I said earlier, usually cell H1 contains "=Today()", but the beauty of using the cell H1, is that I can enter any date into that cell and either get the YTD total from the beginning of that year through to that date. Or, if the date falls within the 3 day window of the interest deposit, it will show me that interest deposit amount. I tried entering 1/6/2024 while using your most recent formula suggestion and it returned #REF! because no interest payments were made between 1/1/2024 and 1/6/2024. This concerns me since it will return a #REF! at the beginning of every year until the first interest deposit is entered into the table. Because of this, I'm going to stick with your earlier formula.
I want to thank you for all your help. You really went above and beyond to come up with a solution to my question. John.

 

[KRice]

You're right...I neglected to properly account for the possibility of no transactions satisfying the criteria. I used a simple "0" in a FILTER function that was intended to be used if nothing were found, but the formula is written in such a way that I expect FILTER to return an array, not a single value...so I needed to specify an array of 0's rather than just one 0, and that caused the error when the INDEX function attempted to locate an array element that did not exist. I did this correctly in one part of the formula, but not another. I've addressed that by introducing one more variable named "empz"...short for empty zeroes, which does nothing more that create an array of eleven 0's, one for every column in Table2. And this "empz" array is used by the FILTER functions where appropriate to account for nothing being found. Here are two versions of the formula:

1. (yellow) one based on your initial SUMIFS approach, but resolving the odd bit where dates were being summed...now using TEXTJOIN and FILTER to find and display the dates, and making the date range consistent among all parts of the formula; and
2. (green) the other based on my suggestion to not use SUMIFS so that common filter components can be reused in other parts of the formula, and with the "empz" improvement described above

Both of these should work correctly if nothing is found, returning 0.00.

MrExcel_20240311.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1								1/7/2024
2		1/5 - 1/7 - 30.00
3		1/5 - 1/7 - 30.00
4
5	Record	DATE	Num	Withdrawal	DEPOSIT	DED	CLR	Balance	CTG	Description	Memo
6		12/31/2023			5		R		Interest
7		1/5/2024			10		R		Interest
8		1/6/2024			1				Interest
9		1/7/2024			20		C		Interest
10		1/10/2025			11		R		Interest
Sheet3

Cell Formulas
Range	Formula
B2	B2	=IF(SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1,Table2[CTG],"Interest")=0,"YTD = "&TEXT(SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&DATE(YEAR(H1),1,1),Table2[DATE],"<="&H1,Table2[CTG],"Interest"),"0.00"),TEXTJOIN(" - ",TRUE,TEXT(FILTER(Table2[DATE],(Table2[DATE]>=H1-2)*(Table2[DATE]<=H1)*(Table2[CTG]="Interest")*(Table2[CLR]<>""),COLUMN(Table2)^0-1),"m/d"))&" - "&TEXT(SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1,Table2[CTG],"Interest",Table2[CLR],"<>"),"0.00"))
B3	B3	=LET(empz,COLUMN(Table2)^0-1,fary,(Table2[DATE]<=H1)*(Table2[CTG]="Interest")*(Table2[CLR]<>""), fmi,FILTER(Table2,fary*(Table2[DATE]>=(H1-2)),empz), smi,SUM(INDEX(fmi,,5)), IF(smi=0, "YTD = "&TEXT(SUM(INDEX(FILTER(Table2,fary*(YEAR(Table2[DATE])=YEAR(H1)),empz),,5)),"0.00"),TEXTJOIN(" - ",TRUE,TEXT(INDEX(fmi,,2),"m/d"),TEXT(smi,"0.00"))))