Help with SUMPRODUCT

[Sean15]

Hi:

Formula in B3 and B5 is not returning required value.
Could you help please?

Ana North & South rents.xlsm
	A	B	C	D	E	F	G
1		06/01/23		Required value
2	Rent - Tax	6.87		6.87
3	Rent	305.54		298.67
4	Water Reimbursement - Tax	0.44		0.44
5	Water Reimbursement	20.44		20.00
6	Check Scan - Pete	(1,911.43)		(1,911.43)
7	Electric Reimbursement	10.00		10.00
8	Online Payment Fee 5/5/2023	(225.00)		(225.00)
9	Non-Refundable Move In Fee (FP)	0.00		0.00
10	Online Administrative Fee	0.00		0.00
11	Online Application Fee	0.00		0.00
12		(1,793.14)
13
14
15					Date	Description	Cost
16					07/01/23	Rent - Tax	25.76
17					07/01/23	Rent	1,120.00
18					07/01/23	Water Reimbursement - Tax	1.65
19					07/01/23	Water Reimbursement	75.00
20					06/26/23	Check Scan - Pete	(678.72)
21					06/23/23	Check Scan - pete 6/26/2023	(232.71)
22					06/23/23	Check Scan - Pete 6/26/2023	(1,000.00)
23					06/23/23	Rent - Tax	6.87
24					06/23/23	Rent	298.67
25					06/23/23	Water Reimbursement - Tax	0.44
26					06/23/23	Water Reimbursement	20.00
27					06/23/23	Electric Reimbursement	10.00
28					06/23/23	Online Payment Fee 5/5/2023	(225.00)
29					05/04/23	Non-Refundable Move In Fee (FP)	350.00
30					05/04/23	Online Administrative Fee	150.00
31					05/04/23	Online Application Fee	75.00
test

Cell Formulas
Range	Formula
B2:B11	B2	=SUMPRODUCT(--(ISNUMBER(SEARCH($A2,$F$16:$F$31))),--($E$16:$E$31>=B$1),--($E$16:$E$31<=EOMONTH(B$1,0)),$G$16:$G$31)
B12	B12	=SUM(B2:B11)

 

[Anonymous1378]

As "rent" is a subset of "rent - tax", the values for "rent - tax" will be included in the SEARCH() for "rent". Try using MATCH() instead?

Book87
	A	B	C	D	E	F	G
1		01-Jun-23		Required value
2	Rent - Tax	6.87		6.87
3	Rent	298.67		298.67
4	Water Reimbursement - Tax	0.44		0.44
5	Water Reimbursement	20		20
6	Check Scan - Pete	-678.72		-1911.43
7	Electric Reimbursement	10		10
8	Online Payment Fee 5/5/2023	-225		-225
9	Non-Refundable Move In Fee (FP)	0		0
10	Online Administrative Fee	0		0
11	Online Application Fee	0		0
12		-567.74
13
14
15					Date	Description	Cost
16					01-Jul-23	Rent - Tax	25.76
17					01-Jul-23	Rent	1120
18					01-Jul-23	Water Reimbursement - Tax	1.65
19					01-Jul-23	Water Reimbursement	75
20					26-Jun-23	Check Scan - Pete	-678.72
21					23-Jun-23	Check Scan - pete 6/26/2023	-232.71
22					23-Jun-23	Check Scan - Pete 6/26/2023	-1000
23					23-Jun-23	Rent - Tax	6.87
24					23-Jun-23	Rent	298.67
25					23-Jun-23	Water Reimbursement - Tax	0.44
26					23-Jun-23	Water Reimbursement	20
27					23-Jun-23	Electric Reimbursement	10
28					23-Jun-23	Online Payment Fee 5/5/2023	-225
29					04-May-23	Non-Refundable Move In Fee (FP)	350
30					04-May-23	Online Administrative Fee	150
31					04-May-23	Online Application Fee	75
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=SUMPRODUCT(--(ISNUMBER(MATCH($F$16:$F$31,$A2,0))),--($E$16:$E$31>=B$1),--($E$16:$E$31<=EOMONTH(B$1,0)),$G$16:$G$31)
B12	B12	=SUM(B2:B11)

EDIT: Just noticed, I don't think this will work all that well given you have lookup values which are subsets of other lookup values, which you don't want to be included in your sum, but you also have lookup values which are subset of the column you are looking up, which you want to be included in your sum.

 

[Anonymous1378]

Try this wall of text instead, I guess:

Book87
	A	B	C	D	E	F	G
1		01-Jun-23		Required value
2	Rent - Tax	6.87		6.87
3	Rent	298.67		298.67
4	Water Reimbursement - Tax	0.44		0.44
5	Water Reimbursement	20		20
6	Check Scan - Pete	-1911.43		-1911.43
7	Electric Reimbursement	10		10
8	Online Payment Fee 5/5/2023	-225		-225
9	Non-Refundable Move In Fee (FP)	0		0
10	Online Administrative Fee	0		0
11	Online Application Fee	0		0
12		-1800.45
13
14
15					Date	Description	Cost
16					01-Jul-23	Rent - Tax	25.76
17					01-Jul-23	Rent	1120
18					01-Jul-23	Water Reimbursement - Tax	1.65
19					01-Jul-23	Water Reimbursement	75
20					26-Jun-23	Check Scan - Pete	-678.72
21					23-Jun-23	Check Scan - pete 6/26/2023	-232.71
22					23-Jun-23	Check Scan - Pete 6/26/2023	-1000
23					23-Jun-23	Rent - Tax	6.87
24					23-Jun-23	Rent	298.67
25					23-Jun-23	Water Reimbursement - Tax	0.44
26					23-Jun-23	Water Reimbursement	20
27					23-Jun-23	Electric Reimbursement	10
28					23-Jun-23	Online Payment Fee 5/5/2023	-225
29					04-May-23	Non-Refundable Move In Fee (FP)	350
30					04-May-23	Online Administrative Fee	150
31					04-May-23	Online Application Fee	75
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=IF(OR(ISNUMBER(MATCH("*"&A2&"*",$A3:$A$12,0)),ISNUMBER(MATCH("*"&A2&"*",$A$1:$A1,0))),SUMPRODUCT(--(ISNUMBER(MATCH($F$16:$F$31,$A2,0))),--($E$16:$E$31>=B$1),--($E$16:$E$31<=EOMONTH(B$1,0)),$G$16:$G$31),SUMPRODUCT(--(ISNUMBER(SEARCH($A2,$F$16:$F$31))),--($E$16:$E$31>=B$1),--($E$16:$E$31<=EOMONTH(B$1,0)),$G$16:$G$31))
B12	B12	=SUM(B2:B11)

 

[Phuoc]

Try this in B2:

=SUMIFS($G$16:$G$31,$F$16:$F$31,$A2,$E$16:$E$31,">="&B$1,$E$16:$E$31,"<="&EOMONTH(B$1,0))

 

[Sean15]

Try this in B2:

=SUMIFS($G$16:$G$31,$F$16:$F$31,$A2,$E$16:$E$31,">="&B$1,$E$16:$E$31,"<="&EOMONTH(B$1,0))

In B6: formula returns $678.72. Required value is $1,911.43.

B2

=IF(OR(ISNUMBER(MATCH("*"&A2&"*",$A3:$A$12,0)),ISNUMBER(MATCH("*"&A2&"*",$A$1:$A1,0))),SUMPRODUCT(--(ISNUMBER(MATCH($F$16:$F$31,$A2,0))),--($E$16:$E$31>=B$1),--($E$16:$E$31<=EOMONTH(B$1,0)),$G$16:$G$31),SUMPRODUCT(--(ISNUMBER(SEARCH($A2,$F$16:$F$31))),--($E$16:$E$31>=B$1),--($E$16:$E$31<=EOMONTH(B$1,0)),$G$16:$G$31))

Returns all required values. Thank you.
But it's a lot to understand. Does a simpler formula exist?

 

[Anonymous1378]

In B6: formula returns $678.72. Required value is $1,911.43.


Returns all required values. Thank you.
But it's a lot to understand. Does a simpler formula exist?

The formula checks if a lookup value is a subset of another lookup value, then will do a MATCH() if it is part of a lookup value, or a SEARCH() if it isn't. The formula can be further shortened due to the repetition of

--($E$16:$E$31>=B$1),--($E$16:$E$31<=EOMONTH(B$1,0)),$G$16:$G$31

But the underlying front potion will need to be there as long as some lookup values are subsets of others, and you don't want those to MATCH() and not SEARCH().