How to create a formula in Google Sheets that can sum specific cells based on multiple criteria

I am wanting to create a formula that will look at a dozen different cells and sum to the total value of those cells but some cells will not be numbers and I want to exclude those cells in the sum.

For example:

D2=10
H2=IF(B1="BYO","Incl.",5)
M2=IF(A1="standard","Incl.",15)

then I want to have Z2=SUM(D2+H2+M2) but sometimes the values will be numbers other times they will be text.

I was playing around using IFS and trying to use syntax like <> “Incl.” but I couldn’t figure out how to make it work with different cells that may or may not be numeric.

Is there some sort of formula for $\tau(S_n)$?

Let $ G$ be a finite group. Define $ \tau(G)$ as the minimal number, such that $ \forall X \subset G$ if $ |X| > \tau(G)$ , then $ XXX = \langle X \rangle$ . Is there some sort of formula for $ \tau(S_n)$ , for the symmetric group $ S_n$ ?

Here $ XXX$ stands for $ \{abc| a, b, c \in X\}$ .

Similar problems for some different classes of groups are already answered:

1) $ \tau(\mathbb{C}_n) = \lceil \frac{n}{3} \rceil + 1$ , where $ \mathbb{C}_n$ is cyclic of order $ n$ ;

2) Gowers, Nikolov and Pyber proved the fact that $ \tau(\mathrm{SL}(n, p)) \leq 2|\mathrm{SL}(n, p)|^{1-\frac{1}{3(n+1)}}$ for prime $ p$ .

However, I have never seen anything like that for $ S_n$ . It will be interesting to know if there is something…

Importrange (or maybe another part of formula) behaves differently after a short time period

I am using this formula to look for data in column F related to data in column A between two spreadsheets.

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; {IMPORTRANGE("spreadsheetID";"sheet1!A4:A")\ IMPORTRANGE("spreadsheetID";"sheet1!F4:F")}; 2; 0);)) 

for some reason after some while (minutes to days) the formula stops working and I need to change the sheet name from SHEET1 to ‘SHEET1’ and it starts working again. BUT after some time I need to change that back to only SHEET1 because it stopped working again and needs a change to be done.

Reason for this instead of

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A;IMPORTRANGE("spreadsheetID";"sheet1!A4:F"); 6; 0);)) 

is because this seems to take a lot less time to import since it’s only minding two columns instead of A and F PLUS all between those two. And also because sometimes I am using the same formula for columns like DF and that’s a huge amount of data.

How can I create a filter view with MOD custom formula?

I want to split a spreadsheet tab (called main) so to have (say) N = 3 people working on it without interfering with each other.

enter image description here

For the purpose, I thought to create 3 filter views, each containing rows whose ID modulo 3 has the same result.

In a new worksheet, this is easily accomplished with a filter like

 =filter(main!A2:B, MOD(main!A2:A, 3) = 0) 

(replacing 0 with 1 or 2 to get the other IDs) so that I get (in the case of modulo = 0)

enter image description here

How can I achieve this with a filter view with custom condition (which is handy to share separate URL links with collaborators)?

enter image description here

Can every Turing Machine be translated into a SAT formula?

For the proof of “Cook-Levin Theorem”, for a Turing Machine $ M$ that accepts a language $ L \in NP$ and input $ x \in \{0,1\}^*$ , we can create a SAT Formula, that is satisfiable if and only if $ M$ accepts $ x$ . Could we adopt this construction so that for any Turing Machine $ M$ and input $ x \in \{0,1\}^*$ we can create a SAT Formula $ \phi$ that is satisfiable if and only if $ M$ accepts $ x$ (even if this SAT Formula has more than polynomial length of $ |M|$ )? Or would that contradicts Rice’s Theorem?

Edit: As dkaeae correctly pointed out, defining a SAT formula that is satisfiable iff a TM $ M$ accepts an input $ x$ is indeed possible. What I meant to ask though is, whether a reduction in the sense of a computable function exists (albeit not being limited to running in polynomial time, but indeed being somehow limited in the running time).

Having a formula output to a different cell

I have a Google Sheet in the following format:

  ID |  VAL ------------- 1    | dataH 2    | dataW 3    | dataX 4    | dataC 5    | dataG 6    | dataL ...  | ... 

ID and VAL are on cells A1 and B1, respectively.

In A2, I have the following formula: =ArrayFormula(if(ISBLANK(B2:B), "",ROW(A2:A)-1)), which puts in a number in the ID column if there is data in the VAL column.

However, I’d like to be able to sort data, for example from A to Z, while keeping ID constant. However, when I sort VAL, the cell containing the formula, ID 1, gets dragged down to its proper position, but then leaves all cells in the column above it blank.

I have 2 possible solutions: somehow have the formula in A1 (ID) and display ID instead of 1; or have the formula in C1 and output the results starting from A2.

Is there way to do one of the 2 solutions above?

“Determinant” rather than “trace” in the alternative formula “Lefschetz number”

For a self map $ f$ on a topological space $ X$ we replace “trace” with “determinant” in the alternative Lefschetz formula $ $ \Lambda(f)=\sum(-1)^i trace(f^*)|H^i(X,\mathbb{Q})$ $

So we have $ $ \Lambda'(f)=\sum(-1)^i Det(f^*)|H^i(X,\mathbb{Q})$ $

What kind of dynamical information we get with this invariant?(This invariant or any other invariant by replacing trace with some other invariant polynomials,i.e. the coefficients of characteristic polynomials)