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!

 

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

 

[Alex Blakenburg]

Appreciate the feedback. Glad we could help.

 

[Peter_SSs]

I would like ONE formula for the column that I can copy down HUNDREDS of rows

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.

 

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