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?

 

[KRice]

With SUMIFS, you won't be able to perform the OR like that. You'll need to sum two separate SUMIFS statements. The first looks like your "R" version. The second would look like your R version, except change the "R" to "C". Alternatively, you could use other approaches to perform the OR inside one function. Do you have a preference?
Options:

MrExcel_20240311.xlsx
	F	G	H	I
1			1/6/2024
2	36	36
3
4
5	DATE	DEPOSIT	CTG	CLR
6	1/5/2024	5	Interest	R
7	1/6/2024	10	Interest	R
8	1/7/2024	1	Interest	C
9	1/8/2024	20	Interest	R
Sheet3

Cell Formulas
Range	Formula
F2	F2	=SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1+2,Table2[CTG],"Interest",Table2[CLR],"R")+SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1+2,Table2[CTG],"Interest",Table2[CLR],"C")
G2	G2	=SUMPRODUCT(Table2[DEPOSIT],(Table2[DATE]>=(H1-2))*(Table2[DATE]<=(H1+2))*(Table2[CTG]="Interest")*(((Table2[CLR]="R")+(Table2[CLR]="C"))>0))

 

[JohnZ1156]

With SUMIFS, you won't be able to perform the OR like that. You'll need to sum two separate SUMIFS statements. The first looks like your "R" version. The second would look like your R version, except change the "R" to "C". Alternatively, you could use other approaches to perform the OR inside one function. Do you have a preference?
Options:

MrExcel_20240311.xlsx
	F	G	H	I
1			1/6/2024
2	36	36
3
4
5	DATE	DEPOSIT	CTG	CLR
6	1/5/2024	5	Interest	R
7	1/6/2024	10	Interest	R
8	1/7/2024	1	Interest	C
9	1/8/2024	20	Interest	R
Sheet3

Cell Formulas
Range	Formula
F2	F2	=SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1+2,Table2[CTG],"Interest",Table2[CLR],"R")+SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1+2,Table2[CTG],"Interest",Table2[CLR],"C")
G2	G2	=SUMPRODUCT(Table2[DEPOSIT],(Table2[DATE]>=(H1-2))*(Table2[DATE]<=(H1+2))*(Table2[CTG]="Interest")*(((Table2[CLR]="R")+(Table2[CLR]="C"))>0))

Thanks KRice, but now I'm having the similar problem with the date.
It seems to only work with "R" in the CLR column, but not with "C".
I attached your suggestion to the end of this formula. (BOLD)
Naturally, your part works great !!! It's my part that is not working properly.

=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],"R"),"m/d")&" - "&SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1+2,Table2[CTG],"Interest",Table2[CLR],"R")+SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1+2,Table2[CTG],"Interest",Table2[CLR],"C"))

 

[KRice]

The first part of your formula is checking whether the SUMIFS

SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1,Table2[CTG],"Interest")

returns a 0, and if true, it looks like you could simply use "YTD = 0.00". You don't need to repeat the SUMIFS to generate 0 again...you already know that it is 0.

And if the expression above is not 0, then you have a long expression beginning with TEXT(SUMIFS... One of the first terms in there is SUMIFS(Table2[DATE]...why are you summing dates? I'm not sure what you want to do there. Right now, you're taking a sum of dates (each date represents the number of days from a baseline "0" date, which is relative to a day over 100 years ago). And then the sum of those dates is expressed in a text format "m/d". For example, if I took today's date 11 March 2024, the numeric value is 45363, and tomorrow's date value is 45364. If I add them, I get 90725. And if I express that sum in terms of TEXT "m/d", I'm saying to tell me the month and day that is 90725 days from day 0. I'm using Excel on a Windows machine, so my 0 date is 1/0/1900, in other words, the first day of the Excel calendar is January 1, 1900. So jumping forward from the 0 date by 90725 days takes me to 23 May 2148, and when expressed in "m/d" format, I'll get the result "5/23". Is this what you want? I would assume you want a date associated directly with some of the deposit items.

The latter part of the formula looks okay...there you are using the two SUMIFS method for creating the sum of C or R. But the TEXT(SUMIFS part of the expression is the problem. Let me know what that's supposed to do.

 

[JohnZ1156]

The first part of your formula is checking whether the SUMIFS

SUMIFS(Table2[DEPOSIT],Table2[DATE],">="&H1-2,Table2[DATE],"<="&H1,Table2[CTG],"Interest")

returns a 0, and if true, it looks like you could simply use "YTD = 0.00". You don't need to repeat the SUMIFS to generate 0 again...you already know that it is 0.

And if the expression above is not 0, then you have a long expression beginning with TEXT(SUMIFS... One of the first terms in there is SUMIFS(Table2[DATE]...why are you summing dates? I'm not sure what you want to do there. Right now, you're taking a sum of dates (each date represents the number of days from a baseline "0" date, which is relative to a day over 100 years ago). And then the sum of those dates is expressed in a text format "m/d". For example, if I took today's date 11 March 2024, the numeric value is 45363, and tomorrow's date value is 45364. If I add them, I get 90725. And if I express that sum in terms of TEXT "m/d", I'm saying to tell me the month and day that is 90725 days from day 0. I'm using Excel on a Windows machine, so my 0 date is 1/0/1900, in other words, the first day of the Excel calendar is January 1, 1900. So jumping forward from the 0 date by 90725 days takes me to 23 May 2148, and when expressed in "m/d" format, I'll get the result "5/23". Is this what you want? I would assume you want a date associated directly with some of the deposit items.

The latter part of the formula looks okay...there you are using the two SUMIFS method for creating the sum of C or R. But the TEXT(SUMIFS part of the expression is the problem. Let me know what that's supposed to do.

First of all, thank you soooo much for your help. It's obvious I'm still trying to get a handle on all of these functions.
If the date of the Interest payment (or in your example "deposit") is >= H1-2 and the date of the Interest payment (or in your example "deposit") is < = H1, and the CTG = "Interest" show the date "m/d" and the amount "0.00". In your example, H1 = 1/6/24, therefore the formula should return 1/6 - 10.00. My formula works OK if the CLR is an "R", but not if it's a "C".

 

[KRice]

Okay, thanks for the explanation. As currently written, the formula computes two things, combines them into a text string, separated by a hyphen, and returns the result to a single cell. The two things computed have slightly different constraints, and that may not be correct...but please review:
For the sum of interest payments: [DATE]>=(H1-2), [DATE]<=(H1+2), [CTG]="Interest", and [CLR]="R" OR "C"
For the "date" to be reported: [DATE]>=(H1-2), [DATE]<=H1, [CTG]="Interest", and [CLR]="R"
Note that the date ranges are different, as the sum of interest payments considers dates within two days on either side of H1, while the "date" to be reported considers those only on the one side of H1 (occurring after two days before H1, but not after H1...is that clear? This same potential issue with the date range to consider also applies to the initial IF condition in the formula you posted, so I'd like to confirm...do you want the same date range for everything, or are the date ranges intended to be different for some reason.

1. It sounds as if you want the "date" reported to apply for interest payments when [CLR]="R" OR "C", is that correct?
2. Do you want the "date" reported also to occur within the same H1+/- 2 days (i.e., within two days on either side of H1), rather than within 2 days on only one side of H1?
3. And if there are multiple entries satisfying these entries, I understand you want the sum of the interest payments, but what do you want to see for the multiple "dates". For example, if there are two interest payments satisfying the constraints (one on 1/6/2024 and another on 1/7/2024), do you want "1/6" or "1/7" or "1/6, 1/7" or "1/6 1/7" or "1/6-1/7" to appear in the results for the first part of the text string?...I'm trying to understand how multiple different dates should be reported as a "date".

Also, just to confirm, your profile shows you are using Excel 2021...so I believe you have the LET, FILTER, and TEXTJOIN functions. Based on the two sets of results needed and the requirement to combine them, I'm thinking a different formula approach would be better. Let me know about the three questions above, and I'll post back with an alternative formula.

 

[JohnZ1156]

Okay, thanks for the explanation. As currently written, the formula computes two things, combines them into a text string, separated by a hyphen, and returns the result to a single cell. The two things computed have slightly different constraints, and that may not be correct...but please review:
For the sum of interest payments: [DATE]>=(H1-2), [DATE]<=(H1+2), [CTG]="Interest", and [CLR]="R" OR "C"
For the "date" to be reported: [DATE]>=(H1-2), [DATE]<=H1, [CTG]="Interest", and [CLR]="R"
Note that the date ranges are different, as the sum of interest payments considers dates within two days on either side of H1, while the "date" to be reported considers those only on the one side of H1 (occurring after two days before H1, but not after H1...is that clear? This same potential issue with the date range to consider also applies to the initial IF condition in the formula you posted, so I'd like to confirm...do you want the same date range for everything, or are the date ranges intended to be different for some reason.

1. It sounds as if you want the "date" reported to apply for interest payments when [CLR]="R" OR "C", is that correct?
2. Do you want the "date" reported also to occur within the same H1+/- 2 days (i.e., within two days on either side of H1), rather than within 2 days on only one side of H1?
3. And if there are multiple entries satisfying these entries, I understand you want the sum of the interest payments, but what do you want to see for the multiple "dates". For example, if there are two interest payments satisfying the constraints (one on 1/6/2024 and another on 1/7/2024), do you want "1/6" or "1/7" or "1/6, 1/7" or "1/6 1/7" or "1/6-1/7" to appear in the results for the first part of the text string?...I'm trying to understand how multiple different dates should be reported as a "date".

Also, just to confirm, your profile shows you are using Excel 2021...so I believe you have the LET, FILTER, and TEXTJOIN functions. Based on the two sets of results needed and the requirement to combine them, I'm thinking a different formula approach would be better. Let me know about the three questions above, and I'll post back with an alternative formula.

1. Yes for both "C" or "R"
2. No, not H1 + / - 2 days. Two days on only 1 side of H1. (i.e. your H1 = 1/6/2024, then on 1/6, 1/7, and 1/8 it should return 1/6 - 10.00, else YTD = "total for year up to H1")
3. I see where you're getting confused. Interest payments are only once a month. Your spreadsheet has payments on consecutiove days. That's my fault, I'm sorry, I didn't make that clear. Just the date of the deposit " - " the amount. (in your example, I'd like to see 1/6 - 10.00, on 1/6, 1/7 and 1/8. Otherwise, YTD for the entire year from 1/1/year(H1) to H1. H1 always contains "=Today()"

I'm sorry this is taking up so much of your time.

 

[JohnZ1156]

1. Yes for both "C" or "R"
2. No, not H1 + / - 2 days. Two days on only 1 side of H1. (i.e. your H1 = 1/6/2024, then on 1/6, 1/7, and 1/8 it should return 1/6 - 10.00, else YTD = "total for year up to H1")
3. I see where you're getting confused. Interest payments are only once a month. Your spreadsheet has payments on consecutiove days. That's my fault, I'm sorry, I didn't make that clear. Just the date of the deposit " - " the amount. (in your example, I'd like to see 1/6 - 10.00, on 1/6, 1/7 and 1/8. Otherwise, YTD for the entire year from 1/1/year(H1) to H1. H1 always contains "=Today()"

I'm sorry this is taking up so much of your time.

One more thing.
The CLR column only has 3 options, "R", "C", or blank. Besides R + C, if the record is "blank" in the CLR column, I would like all calculations to "omit" that record.

 

[KRice]

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.

 

[KRice]

Here is an alternative formula that relies on the LET function, which allows us to assign intermediate quantities to variable names. This is handy when certain quantities need to be used multiple times (to reduce redundancy) or to make it convenient to refer to quantities by a short convenient name rather than a long complex formula that would otherwise be nested inside another formula (so it can improve understanding of the formula). A preliminary filtering array called "fary" is formed, and it accounts for [DATE]<=H1, [CTG]="Interest", and [CLR]<>"" (which means only "R" and "C" will be considered). This array isn't quite what we need, as the other part of the date criteria needs to be addressed, but that other part depends on whether the sum of interest is 0...in which case, you want the YTD result. So we filter the data table and include the "fary" filtering array multiplied by [DATE]>=(H1-2), which means we'll get a subset of Table 2 where all criteria are met for dates within the past two days of H1...and this subset array is called "fmi"...short for filtered monthly interest. We sum the 2nd column of "fmi", which is the column containing the interest value(s), and call this result "smi"...short for sum of monthly interest. Then in the final step, we use an IF function to tailor the results: If smi=0, then we recompute the year-to-date interest by reusing that partial "fary" array and multiplying it by an array that also requires any dates to be from the same year as H1...and then summing the 2nd column of the results:

SUM(INDEX(FILTER(Table2,fary*(YEAR(Table2[DATE])=YEAR(H1)),0),,2))

...and this YTD result is reported in 0.00 format and prefaced by "YTD = ".
And in the final step, if smi<>0, then you do have some monthly interest, so the date(s) of those transactions are joined together, separated by hyphens, followed by the sum in 0.00 format.
There is one issue that I'm not sure about, and that concerns whether multiple interest transactions might be found within the constraints of the filters. I'm assuming that is possible because you initially used SUMIFS, as though there might be more than one. If that is true, then you could have multiple dates, and I'm still not sure how you want those displayed. I've included all of them, separated by hyphens as shown in this example, but let me know if you want something different. If there is only one date, then you'll see only one date followed by a hyphen, and followed by the interest amount.

MrExcel_20240311.xlsx
	F	G	H	I
1			1/7/2024
2
3	1/5 - 1/7 - 21.00
4
5	DATE	DEPOSIT	CTG	CLR
6	12/31/2023	5	Interest	R
7	1/3/2024	10	Interest	R
8	1/5/2024	1	Interest	C
9	1/7/2024	20	Interest	R
10	1/10/2024	11	Interest	R
Sheet3

Cell Formulas
Range	Formula
F3	F3	=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"))))