SUM Using Multiple Criteria to Determine What is Counted & How [Part 2]

Question

I am trying to create an array formula in Google Sheets to calculate the additional savings resulting from applying a discount pretax.

It is based on a simple calculation but becomes more complex because of additional conditions. For clarity I explain the Basic Functionality first, then the Extra Conditions.

Basic Functionality

I have a single Tax Rate for each row in column B followed by multiple column pairs (C:D , E:F , G:H), one pair for each discount.

In each column pair, the first column Discount <id> contains the dollar amount and the second column Pretax <id> contains a flag indicating if the discount was applied pretax.

For each column pair, if the pretax flag is set to "Y", the basic tax savings calculation is the 'discount amount multiplied by the tax rate':

=IF(pretax="Y", discount * tax rate,)

The formula I am trying to create should go in N2. This formula would return the total Tax Savings for each row by calculating the SUM of the savings from each column pair.

Sample Data: Basic Functionality

Note: Labels have been shortened to save space

	B	C	D	E	F	G	H	N
1	Rate	Disc_1	Pre_1	Disc_2	Pre_2	Disc_3	Pre_3	Tax Savings
2								[formula_goes_here]
3	5 %	$ 1.00	Y	$ 1.00	Y	$ 1.00	Y	$ 0.15
4	5 %	$ 1.00	Y	$ 1.00	Y	$ 1.00	N	$ 0.10
5	5 %	$ 1.00	N	$ 1.00	N	$ 1.00	N

• In the table above:
  N3: (C3*B3)+(E3*B3)+(G3*B3)=0.15 (Y,Y,Y)
  N4: (C4*B4)+(E4*B4)=0.10 (Y,Y,N)
  N5:   (N,N,N)

Extra Conditions

These conditions override the basic calculation. ?? is always returned by the formula if any condition that follows is matched in any column pair.

?? is returned where there is a:

1. question mark ? anywhere in any value;
2. numerical value in discount column but paired pretax column isn't Y or N
3. value in pretax column but paired discount column isn't a numerical value.

Sample Data: Extra Conditions

Note: Only a single column pair needs to match an extra condition to override the calculation for the entire row (all pairs).

	B	C	D	E	F	G	H	N
1	Rate	Disc_1	Pre_1	Disc_2	Pre_2	Disc_3	Pre_3	Tax Savings
2								[formula_goes_here]
3	5 %
4	5 %			$ 1.00	Y	$ 1.00	Y	$ 0.10
5	5 %	$ 1.00	Y	$ 1.00	Y	$ 1.00	Y	$ 0.15
6	5 %	$ 1.00	N	$ 1.00	Y	$ 1.00	Y	$ 0.10
7	5 %	$ 1.00	??	$ 1.00	Y	$ 1.00	Y	??
8	5 %	$ 1.00		$ 1.00	Y	$ 1.00	Y	??
9	5 %		Y	$ 1.00	Y	$ 1.00	Y	??
10	5 %		N	$ 1.00	Y	$ 1.00	Y	??
11	5 %		??	$ 1.00	Y	$ 1.00	Y	??
12	5 %		?	$ 1.00	Y	$ 1.00	Y	??
13	5 %	???		$ 1.00	Y	$ 1.00	Y	??

In the example above the formula returns ?? for rows 7 to 13. The table below shows the conditions that matched and triggered those ?? results.

Conditions that Matched

Row	Tax Rate	Dis_1	Pre_1	Condition 1	Condition 2	Condition 3
				? anywhere	Disc=# Pre<>Y/N	Pre<>"" Disc=NOT(#)
7	5 %	$ 1.00	??	✓	✓
8	5 %	$ 1.00			✓
9	5 %		Y			✓
10	5 %		N			✓
11	5 %		??	✓		✓
12	5 %		?	✓		✓
13	5 %	???		✓

I am looking for help with a single formula in N2 that will behave as described.

Link to editable demo sheet

---

I have a prior question about other calculations in the the same worksheet:
SUM Using Multiple Criteria to Determine What is Counted & How [Part 1]

Answer 1

You may try:

=byrow(C3:J,lambda(Σ,let(odd,choosecols(Σ,1,3,5,7),even,choosecols(Σ,2,4,6,8),
      index(ifs(iferror(find("?",join(,Σ))),"??",counta(odd)>counta(even),"??",(counta(odd)=0)*(counta(even)>0),"??",counta(odd)=0,,1,sum(odd*switch(even,"Y",1,"N",0,))*offset(index(Σ,,1),,-1))))))