SUM Using Multiple Criteria to Determine What is Counted & How [Part 1]
[Edited: This has been solved but I made a mess of the original question, so I've edited it for clarity and posterity.]
I'm looking to add the numbers from multiple columns in a row, to come up with some totals. But which columns are counted and how they are used needs to be based on multiple criteria.
Here is an editable demo sheet, noted for clarity and with the desired results: https://docs.google.com/spreadsheets/d/11Ixj8D5Wl8ZEG2M-9Z1AdGNDpmCP5h9TPLFEdCylQcg/
Part 1: Calculating K ("Total Discounts Before Tax")
Purpose of K:
The goal for K is simply to find the total value of the discounts before any tax calculations.
Finally, whenever there is an unknown factor that prevents a directly related 100% known measure for K, then K="??" so it's clear that information is missing and still needed.
Requirements for Results of K: (See M for 'Desired Results')
A single formula used in Header Row K2. Something like an ArrayFormula so that it can be used for the whole column and can have a visible text "LABEL" shown in the cell. [eg "={"LABEL"; ArrayFormula() }" ]
If C or E or G or I has even one "?", then K="??" [eg E8]
If C="" and D<>"" (same for E&F,G&H,I&J), then K="??" [eg C6:D6, I3:J3]
If all 'Discount' and 'is Pre-Tax Columns="" (columns C through J), then K="" [eg ROW13]
If C=ISNUMBER add C to others (C+E+G+I), but only where they are also =ISNUMBER (After meeting above conditions first) [ROW4, ROW5]
I would be extremely grateful for any help! Thank you!
Part 2: Calculating N ("Sales Tax Savings Because of Discounts")
There's a second even more complicated part that I'm also trying to accomplish in another posted question here: SUM Using Multiple Criteria to Determine What is Counted & How [Part 2]
=byrow(C3:J,lambda(Σ,let(odd,choosecols(Σ,1,3,5,7),even,choosecols(Σ,2,4,6,8),
index(ifs(iferror(find("?",join(,odd))),"??",sum((odd="")*(len(even))),"??",counta(odd)=0,,1,sum(odd))))))
