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!

 

[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.

 

[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

 

[DeGeorge]

Fluff & Alex

Both solutions worked, however Fluff's solution is a little easier to manipulate. Thank you both for your help with this! I really appreciate it.

 

[DeGeorge]

Fluff and Alex

Both solutions worked, however Fluff's solution is a little easier to manipulate. Thank you both for your help with this! I really appreciate it.

 

[DeGeorge]

Would an option where you didn't need to copy anywhere, just put in the top row be worth considering?

DeGeorge.xlsm
	B	C	D
1	Date	Amount	Comments
2	1/04/2019	($25.71)	Payment
3	1/04/2019	($7.99)	Payment
4	3/04/2019	($241.92)	Payment
5	3/04/2019	$150.00	Deposit
6	5/04/2019	$107.00	Deposit
7	5/04/2019	$11.99	Deposit
8	8/04/2019	($53.40)	Payment
9	8/04/2019	($9.59)	Payment
10	9/04/2019	($34.33)	Payment
11	2/05/2019	($45.00)	Payment
12	2/05/2019	$25.00	Deposit
13	2/05/2019	($12.22)	Payment
14	2/05/2019	$35.00	Deposit
15	17/05/2019	($6.93)	Payment
16	20/05/2019	($79.73)	Payment
17	21/05/2019	$6,336.00	Deposit
18	11/12/2023	($200.32)	Payment
19	22/05/2019	($118.01)	Payment
20	28/05/2019	($2.90)	Payment
21	30/05/2019	$436.00	Deposit
22	30/05/2019	($19.37)	Payment
23	31/05/2019	$0.28	Deposit
24	3/06/2019	($76.15)	Payment
25	3/06/2019	$364.00	Deposit
26	3/06/2019	($31.74)	Payment
27	4/06/2019	($47.50)	Payment
28	5/06/2019	($95.00)	Payment
29	5/06/2019	$765.00	Deposit
30	5/06/2019	($28.50)	Payment
31	10/06/2019	($108.16)	Payment
32	10/06/2019	$679.00	Deposit
33	10/06/2019	($26.80)	Payment
34	10/06/2019	$345.00	Deposit
35	10/06/2019	($19.99)	Payment
36	10/06/2019	($12.79)	Payment
37	10/06/2019	($12.78)	Payment
38	10/06/2019	$234.00	Deposit
39	1/03/2023	($100.16)	Payment
40	12/06/2019	$234.00	Deposit
41	12/06/2019	$243.00	Deposit
42	13/06/2019	($4.66)	Payment
Sheet1

DeGeorge.xlsm
	A	B	C
1	Month	Payments	Deposits
2	Apr-19	($372.94)	$268.99
3	May-19	($284.16)	$6,832.28
4	Jun-19	($464.07)	$2,864.00
5	Jul-19	$0.00	$0.00
6	Aug-19	$0.00	$0.00
7	Sep-19	$0.00	$0.00
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=BYROW(A2:A7,LAMBDA(r,SUM(FILTER(Sheet1!C2:C42,(TEXT(Sheet1!B2:B42,"my")=TEXT(r,"my"))*(Sheet1!D2:D42="Payment"),0))))
C2:C7	C2	=BYROW(A2:A7,LAMBDA(r,SUM(FILTER(Sheet1!C2:C42,(TEXT(Sheet1!B2:B42,"my")=TEXT(r,"my"))*(Sheet1!D2:D42="Deposit"),0))))
Dynamic array formulas.

Frank

This is awesome! I never knew anything like this could be done. Need to read up on LAMBDA!