Having issue with SUMIFS()

[DeGeorge]

I'm trying to sum a range based on multiple other columns.

I want to sum C3:C41 if G3:G41 = "Payment" and Month of B3:B41 = "4" and Year of B3:B41 = "2019"

So I tried this =SUMIFS(C3:C41,G3:G41,"Payment",Month(B3:B41),"4",Year(B3:B41),"2019"), and it won't resolve.

Separately, Month(B3:B41) returns an array of the months of the dates in that range. Year(B3:B41) returns an array of years of the dates in that range. When I add the criteria for month or year, I get TRUE or FALSE but it only works on 1 cell at at time, not a range. I get a spill range.

I'm guessing that Month() and Year() can't be used as criteria in a SUMIFS because of the spill?

Any ideas? Thanks!

 

[Fluff]

The criteria range must be a range & not an array.
Try

=SUMIFS(C3:C41,G3:G41,"Payment",B3:B41,">="&DATE(2019,4,1),B3:B41,"<"&DATE(2019,5,1))

 

[DeGeorge]

The criteria range must be a range & not an array.
Try

=SUMIFS(C3:C41,G3:G41,"Payment",B3:B41,">="&DATE(2019,4,1),B3:B41,"<"&DATE(2019,5,1))

Thanks for replying, Fluff. The problem I have with that is I'd have to enter the values in the Date() formula. I have a table with over 1,000 records with dates between 3/1/2019 and the present. That's why I wanted to use something more flexible like Month() and Year().

 

[Peter_SSs]

The problem I have with that is I'd have to enter the values in the Date() formula.

How is that different to entering the month and year values in the formula you were attempting?

What about putting the month and year of interest in cells on the worksheet? eg month of interest (4) in J1 and year of interest (2019) in J2 and then refer to those in @Fluff's formula?

=SUMIFS(C3:C41,G3:G41,"Payment",B3:B41,">="&DATE(J2,J1,1),B3:B41,"<"&DATE(J2,J1+1,1))

If you didn't want the values in the worksheet and only wanted to enter them in the formula once, you could also use this though SUMPROIDUCT is less efficient than SUMIFS

=SUMPRODUCT(--(G3:G41="Payment"),--(TEXT(B3:B41,"myyyy")="42019"),C3:C41)

 

[Alex Blakenburg]

I was going to make a similar comment about Fluff's formula and I think it becomes more obvious if you throw in the LET statement.
Filter is another option

20240324 SumIfs Date Range DeGeorge.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Date	Amt	ColD	ColE	ColF	Tran Type		Summary
2		10/12/2018	100				Receipt		Fluff	950
3		25/12/2018	200				Balance		Fluff Modified	950
4		9/01/2019	300				Payment		Filter	950
5		24/01/2019	400				Receipt
6		8/02/2019	500				Balance
7		23/02/2019	600				Payment
8		10/03/2019	700				Receipt
9		25/03/2019	800				Balance
10		9/04/2019	900				Payment
11		15/04/2019	50				Payment
12		24/04/2019	1000				Receipt
13		9/05/2019	1100				Balance
14		24/05/2019	1200				Payment
15		8/06/2019	1300				Receipt
16		23/06/2019	1400				Balance
17		8/07/2019	1500				Payment
18		23/07/2019	1600				Receipt
19		7/08/2019	1700				Balance
Sheet1

Cell Formulas
Range	Formula
J2	J2	=SUMIFS(C2:C41,G2:G41,"Payment",B2:B41,">="&DATE(2019,4,1),B2:B41,"<"&DATE(2019,5,1))
J3	J3	=LET(yr,2019,mth,4,SUMIFS(C2:C41,G2:G41,"Payment",B2:B41,">="&DATE(yr,mth,1),B2:B41,"<"&DATE(yr,mth+1,1)))
J4	J4	=SUM(FILTER($C$2:$C$41,($G$2:$G$41="Payment")*(MONTH($B$2:$B$41)=4)*(YEAR($B$2:$B$41)=2019),0))

 

[DeGeorge]

Hi Fluff, Peter, and Alex

I don't think I'm explaining myself very well. I apologize. I hope this is a better explanation. I would like ONE formula for the column that I can copy down HUNDREDS of rows based on the first tables' MONTH field and the second tables DATE and COMMENT field. THanks!

I have this table on one tab:

Month	Payments	Deposits
April 2019​	I WANT TOTAL OF APRIL 2019 PAYMENTS FROM OTHER TABLE HERE	I WANT TOTAL OF APRIL 2019 DEPOSITS FROM OTHER TABLE HERE
May 2019​
June 2019​
July 2019​
August 2019​
September 2019​

I have this table on another tab

Date	Amount	Comments
04/01/2019​	$ (25.71)	Payment
04/01/2019​	$ (7.99)	Payment
04/03/2019​	$ (241.92)	Payment
04/03/2019​	$ 150.00	Deposit
04/05/2019​	$ 107.00	Deposit
04/05/2019​	$ 11.99	Deposit
04/08/2019​	$ (53.40)	Payment
04/08/2019​	$ (9.59)	Payment
04/09/2019​	$ (34.33)	Payment
05/02/2019​	$ (45.00)	Payment
05/02/2019​	$ 25.00	Deposit
05/02/2019​	$ (12.22)	Payment
05/02/2019​	$ 35.00	Deposit
05/17/2019​	$ (6.93)	Payment
05/20/2019​	$ (79.73)	Payment
05/21/2019​	$ 6,336.00	Deposit
12/11/2023​	$ (200.32)	Payment
05/22/2019​	$ (118.01)	Payment
05/28/2019​	$ (2.90)	Payment
05/30/2019​	$ 436.00	Deposit
05/30/2019​	$ (19.37)	Payment
05/31/2019​	$ 0.28	Deposit
06/03/2019​	$ (76.15)	Payment
06/03/2019​	$ 364.00	Deposit
06/03/2019​	$ (31.74)	Payment
06/04/2019​	$ (47.50)	Payment
06/05/2019​	$ (95.00)	Payment
06/05/2019​	$ 765.00	Deposit
06/05/2019​	$ (28.50)	Payment
06/10/2019​	$ (108.16)	Payment
06/10/2019​	$ 679.00	Deposit
06/10/2019​	$ (26.80)	Payment
06/10/2019​	$ 345.00	Deposit
06/10/2019​	$ (19.99)	Payment
06/10/2019​	$ (12.79)	Payment
06/10/2019​	$ (12.78)	Payment
06/10/2019​	$ 234.00	Deposit
03/01/2023​	$ (100.16)	Payment
06/12/2019​	$ 234.00	Deposit
06/12/2019​	$ 243.00	Deposit
06/13/2019​	$ (4.66)	Payment

 

[Alex Blakenburg]

Why not use a pivot table ?
In terms of a formula is your Month column a date or text ?
(If you are not sure change the format to a different date format or to General if nothing changes its text if it changes its a date - ctrl+Z to put it back if it does change)
Also please provide the sheet name for the data sheet and if its an excel table the table name.

 

[Alex Blakenburg]

I am login off for the night. Assuming your month is actually a date and your sheet for the data is Sheet2, try the below.
Since your headings are plural (Payments) and your transaction type is singular (Payment) I have had to trim the "s" off your heading to use as criteria

20240324 SumIfs Date Range DeGeorge v02.xlsx
	A	B	C
1	Month	Payments	Deposits
2	April 2019	(372.94)	268.99
3	May 2019	(284.16)	6,832.28
4	June 2019	(464.07)	2,864.00
5	July 2019	-	-
6	August 2019	-	-
7	September 2019	-	-
Sheet1

Cell Formulas
Range	Formula
B2:C7	B2	=LET(yr,YEAR($A2), mth,MONTH($A2), tranType,LEFT(B$1,LEN(B$1)-1), SUMIFS(Sheet2!$B$2:$B$42,Sheet2!$C$2:$C$42,tranType,Sheet2!$A$2:$A$42,">="&DATE(yr,mth,1),Sheet2!$A$2:$A$42,"<"&DATE(yr,mth+1,1)))

If your month is a date then it could be simplified to this:

=LET(dt,$A2,
            tranType,LEFT(B$1,LEN(B$1)-1),
            SUMIFS(Sheet2!$B$2:$B$42,Sheet2!$C$2:$C$42,tranType,Sheet2!$A$2:$A$42,">="&dt,Sheet2!$A$2:$A$42,"<="&EOMONTH(dt,0)))

 

[DeGeorge]

I am login off for the night. Assuming your month is actually a date and your sheet for the data is Sheet2, try the below.
Since your headings are plural (Payments) and your transaction type is singular (Payment) I have had to trim the "s" off your heading to use as criteria

20240324 SumIfs Date Range DeGeorge v02.xlsx
	A	B	C
1	Month	Payments	Deposits
2	April 2019	(372.94)	268.99
3	May 2019	(284.16)	6,832.28
4	June 2019	(464.07)	2,864.00
5	July 2019	-	-
6	August 2019	-	-
7	September 2019	-	-
Sheet1

Cell Formulas
Range	Formula
B2:C7	B2	=LET(yr,YEAR($A2), mth,MONTH($A2), tranType,LEFT(B$1,LEN(B$1)-1), SUMIFS(Sheet2!$B$2:$B$42,Sheet2!$C$2:$C$42,tranType,Sheet2!$A$2:$A$42,">="&DATE(yr,mth,1),Sheet2!$A$2:$A$42,"<"&DATE(yr,mth+1,1)))

If your month is a date then it could be simplified to this:

=LET(dt,$A2,
            tranType,LEFT(B$1,LEN(B$1)-1),
            SUMIFS(Sheet2!$B$2:$B$42,Sheet2!$C$2:$C$42,tranType,Sheet2!$A$2:$A$42,">="&dt,Sheet2!$A$2:$A$42,"<="&EOMONTH(dt,0)))

Hi Alex

Month is a date field with custom format mmmm yyyy. So, the values of each are 4/1/2019, 5/1/2019, etc.

Let me play with what you sent me. Have a good night and thanks!

Jim

 

[Fluff]

Another option is like

=SUMIFS(Sheet1!C3:C41,Sheet1!G3:G41,"Payment",Sheet1!B3:B41,">="&A2,Sheet1!B3:B41,"<"&EDATE(A2,1))