SUMIF - What am I doing wrong?

[Demonsguile]

I'd like some help understanding why this formula doesn't work. The formula looks at multiple conditions:
1. Unit # (column J)
2. Fee (column K)
3. Date (column I)

If all three conditions are met, it should return the sum of everything that meets the conditions. However, if I include either "Re Inspection Fee -" or "Late Fee -", the formula returns the respective value. By adding both, it returns nothing. Or, more accurately, it returns 0.00 when it should return something. Why isn't this working? It obviously works as expected for the Unit #. Help?

=SUM(SUMIFS('Ledger - Details'!$L:$L, 'Ledger - Details'!$J:$J, {"UNITA","UNITB","UNITC","UNITD"}, 'Ledger - Details'!$K:$K, {"Re Inspection Fee -","Late Fee -"}, 'Ledger - Details'!$I:$I, AM$1))*-1

 

[KRice]

I believe the issue relates to a mismatch in the array dimensions. With only one criteria listing 4 match items (the Units), the other singular criteria (a fee term and a date) are applied to each of the 1st items with an AND condition. As soon as you add a 2nd dimension to one of the other matching arrays (e.g., the Fees), then the formula attempts to match the conditions differently: UNITA to Re Inspection Fee - and the next is UNITB to Late Fee -, and there is nothing to match with UNITC and UNITD. You could repeat the SUMIFS function inside the SUM (so you'd have two SUMIFS functions), with the first specifying only one fee term and the other specifying the other fee term to see if that returns the expected value.

What version of Excel are you using?

 

[KRice]

You could try adapting this type of approach...here I sum values in column C where there are 3 match items in B and 2 match items in D. The vertical arrays of 1's (uses semicolons) are of whatever length matches the number of match criteria for that item.

Book1
	B	C	D
1
2			10
3
4	a	1	x
5	b	2	x
6	c	3	x
7	b	4	y
8	d	5	x
9	d	6	y
10	e	7	z
Sheet3

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(C4:C10,MMULT(--(B4:B10={"a","b","c"}),{1;1;1}),MMULT(--(D4:D10={"x","y"}),{1;1}))

 

[Demonsguile]

I believe the issue relates to a mismatch in the array dimensions. With only one criteria listing 4 match items (the Units), the other singular criteria (a fee term and a date) are applied to each of the 1st items with an AND condition. As soon as you add a 2nd dimension to one of the other matching arrays (e.g., the Fees), then the formula attempts to match the conditions differently: UNITA to Re Inspection Fee - and the next is UNITB to Late Fee -, and there is nothing to match with UNITC and UNITD. You could repeat the SUMIFS function inside the SUM (so you'd have two SUMIFS functions), with the first specifying only one fee term and the other specifying the other fee term to see if that returns the expected value.

What version of Excel are you using?

Thank you for the quick response. I'm using MS Office Pro Plus 2016.
I modified the formula to include four variables (two of them are bogus, though) to see if that would do the trick. I got the same result, which returned 0.00.

=SUM(SUMIFS('Ledger - Details'!$L:$L, 'Ledger - Details'!$J:$J, {"UNITA","UNITB","UNITC","UNITD"}, 'Ledger - Details'!$K:$K, {"Late Fee -","Re Inspection Fee -","A","B"}, 'Ledger - Details'!$I:$I, AM$1))*-1

 

[Demonsguile]

You could try adapting this type of approach...here I sum values in column C where there are 3 match items in B and 2 match items in D. The vertical arrays of 1's (uses semicolons) are of whatever length matches the number of match criteria for that item.

Book1
	B	C	D
1
2			10
3
4	a	1	x
5	b	2	x
6	c	3	x
7	b	4	y
8	d	5	x
9	d	6	y
10	e	7	z
Sheet3

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(C4:C10,MMULT(--(B4:B10={"a","b","c"}),{1;1;1}),MMULT(--(D4:D10={"x","y"}),{1;1}))

Thank you. I'll take a closer look and see if I can adapt it. I'll be honest in that I'm not quite sure what the {1;1;1} is supposed to do. But, I'll try to figure it out.

 

[KRice]

I got the same result, which returned 0.00

That's likely because of the way the AND condition is applied...the formula is only considering UNITA AND Late Fee, UNIT B AND Re Inspection Fee, UNITC AND A, and UNITD AND B. The last criteria involving column I is applied to all.

Have a look at my suggestion in post #3. With that approach, you'll have to count the number of matching criteria (so 4 for the Units and 2 for the Fees) and construct the vertical arrays {1;1;1;1} and {1;1}, respectively. Essentially, this approach produces intermediate vertical arrays for each of the match criteria (so a 4-column array indicating which rows match each of the Units items, and a 2-column array for which rows match each of the Fees items)...then to convert each of these multi-column arrays into single column arrays indicating that any of the matching terms were found on each row, we mutliply the multi-column array by a vertical array of 1's (so the number of columns in the first array needs to match the number of 1's in the vertical array).

Here is an example similar to yours with a 3 item array for matching in one column, a 2 item array for matching in a second column, and a single item for matching in a third column:

Book1
	B	C	D	E
1
2			7
3
4	a	1	x	dog
5	b	2	x	dog
6	c	3	x	cat
7	b	4	y	dog
8	d	5	x	cat
9	d	6	y	dog
10	e	7	z	dog
Sheet3

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(C4:C10,MMULT(--(B4:B10={"a","b","c"}),{1;1;1}),MMULT(--(D4:D10={"x","y"}),{1;1}),--(E4:E10="dog"))

 

[Demonsguile]

I figured it might be easier if I provided the actual data-set that I'm working with:

Book1
	A	B	C	D
1	Date	Unit	Description	Amount
2	7/1/2022	Building	Management Fees-	-27.47
3	7/1/2022	UNITC	Rent -	274.67
4	7/1/2022	Building	Management Fees-	-258.7
5	7/1/2022	UNITC	Late Fee -	150
6	7/1/2022	UNITC	Re Inspection Fee -	84.67
7	7/1/2022	UNITC	Rent -	240.33
8	7/1/2022	Building	Yard -	-54.13
9	7/1/2022	UNITD	Repairs-	-125
10	7/1/2022	Building	Yard -	-54.13
11	7/1/2022	UNITB	Rent -	500
12	7/1/2022	UNITD	Rent -	515
13	7/1/2022	UNITD	Late Fee -	35
14	7/1/2022	UNITA	Rent -	475
15	7/1/2022	Building	Management Fees-	-184
16	7/1/2022	UNITC	Rent -	515
17	7/1/2022	Building	Management Fees-	-51.5
Sheet1

I am looking for a way to match three criteria (Unit, Fee, and Date) and sum them if they all match. For example, the formula should return $269.67 (Two late fees and a Reinspection Fee).

 

[KRice]

Try this:

Book1
	A	B	C	D	E	F
1	Date	Unit	Description	Amount		269.67
2	7/1/2022	Building	Management Fees-	-27.47
3	7/1/2022	UNITC	Rent -	274.67
4	7/1/2022	Building	Management Fees-	-258.7
5	7/1/2022	UNITC	Late Fee -	150
6	7/1/2022	UNITC	Re Inspection Fee -	84.67
7	7/1/2022	UNITC	Rent -	240.33
8	7/1/2022	Building	Yard -	-54.13
9	7/1/2022	UNITD	Repairs-	-125
10	7/1/2022	Building	Yard -	-54.13
11	7/1/2022	UNITB	Rent -	500
12	7/1/2022	UNITD	Rent -	515
13	7/1/2022	UNITD	Late Fee -	35
14	7/1/2022	UNITA	Rent -	475
15	7/1/2022	Building	Management Fees-	-184
16	7/1/2022	UNITC	Rent -	515
17	7/1/2022	Building	Management Fees-	-51.5
18
19
20
Sheet4

Cell Formulas
Range	Formula
F1	F1	=SUMPRODUCT(D2:D20,MMULT(--(B2:B20={"UNITA","UNITB","UNITC","UNITD"}),{1;1;1;1}),MMULT(--(C2:C20={"Re Inspection Fee -","Late Fee -"}),{1;1}),--(A2:A20=DATE(2022,7,1)))

 

[Demonsguile]

Try this:

Book1
	A	B	C	D	E	F
1	Date	Unit	Description	Amount		269.67
2	7/1/2022	Building	Management Fees-	-27.47
3	7/1/2022	UNITC	Rent -	274.67
4	7/1/2022	Building	Management Fees-	-258.7
5	7/1/2022	UNITC	Late Fee -	150
6	7/1/2022	UNITC	Re Inspection Fee -	84.67
7	7/1/2022	UNITC	Rent -	240.33
8	7/1/2022	Building	Yard -	-54.13
9	7/1/2022	UNITD	Repairs-	-125
10	7/1/2022	Building	Yard -	-54.13
11	7/1/2022	UNITB	Rent -	500
12	7/1/2022	UNITD	Rent -	515
13	7/1/2022	UNITD	Late Fee -	35
14	7/1/2022	UNITA	Rent -	475
15	7/1/2022	Building	Management Fees-	-184
16	7/1/2022	UNITC	Rent -	515
17	7/1/2022	Building	Management Fees-	-51.5
18
19
20
Sheet4

Cell Formulas
Range	Formula
F1	F1	=SUMPRODUCT(D2:D20,MMULT(--(B2:B20={"UNITA","UNITB","UNITC","UNITD"}),{1;1;1;1}),MMULT(--(C2:C20={"Re Inspection Fee -","Late Fee -"}),{1;1}),--(A2:A20=DATE(2022,7,1)))

You are a genius and a scholar. Thank you! I still am unclear on why it works, but I'm going to figure it out.
Thanks again!

 

[KRice]

Here is another option (green cells) based on my earlier point. Using SUMIFS, you'll need to use no more than one multi-dimensional array because the function will not treat them as OR conditions, and will instead consider only the 1st items of each matching criteria to be an AND condition (this is confusing). So the first row illustrates how the 4-column array for the UNITA, UNITB, UNITC, and UNITD matching returns all 0's (there are no UNITA AND Re Inspection Fee- AND 7/1/2022 rows, etc.). The next row shows the results when the 2nd two-item fee array is split to consider only one of the terms (Re Inspection Fee-). The next row shows the results when the other fee term is used. The next row combines the two previous ones. The last row wraps this 4-column resultant array with a SUM function to obtain the final result.

Book1
	H	I	J	K	L
1	unitA	unitB	unitC	unitD
2	0	0	0	0	trying to combine mismatched arrays with AND condition
3	0	0	84.67	0	using only one multi-column array and splitting other
4	0	0	150	35	using only one multi-column array and splitting other
5	0	0	234.67	35	using the two SUMIFS above and summing them
6	269.67				wrapping the result immediately above with SUM to sum all columns
Sheet4

Cell Formulas
Range	Formula
H2:K2	H2	=SUMIFS(D2:D20,B2:B20,{"UNITA","UNITB","UNITC","UNITD"},C2:C20,{"Re Inspection Fee -","Late Fee -"},A2:A20,DATE(2022,7,1))
H3:K3	H3	=SUMIFS(D2:D20,B2:B20,{"UNITA","UNITB","UNITC","UNITD"},C2:C20,"Re Inspection Fee -",A2:A20,DATE(2022,7,1))
H4:K4	H4	=SUMIFS(D2:D20,B2:B20,{"UNITA","UNITB","UNITC","UNITD"},C2:C20,"Late Fee -",A2:A20,DATE(2022,7,1))
H5:K5	H5	=SUMIFS(D2:D20,B2:B20,{"UNITA","UNITB","UNITC","UNITD"},C2:C20,"Re Inspection Fee -",A2:A20,DATE(2022,7,1)) + SUMIFS(D2:D20,B2:B20,{"UNITA","UNITB","UNITC","UNITD"},C2:C20,"Late Fee -",A2:A20,DATE(2022,7,1))
H6	H6	=SUM(SUMIFS(D2:D20,B2:B20,{"UNITA","UNITB","UNITC","UNITD"},C2:C20,"Re Inspection Fee -",A2:A20,DATE(2022,7,1)) + SUMIFS(D2:D20,B2:B20,{"UNITA","UNITB","UNITC","UNITD"},C2:C20,"Late Fee -",A2:A20,DATE(2022,7,1)))
Dynamic array formulas.