formulas – Google Spreadsheet: Adding together multiple Sum Filter or Sumifs with multiple conditions across multiple sheets?

So I have a budgeting spread sheet. I have an overview page that I need to link to a “Checkings” sheet and a “CC-Chase” sheet.

To calculate the amount spent on a specific category that month I currently use:

I want to add in the same data from my credit card statments so I tried:
=sum(filter(Checkings!$B:$B,Checkings!$C:$C=("Grocery"),Checkings!$A:$A>=("1/1/21")+0,Checkings!$A:$A<=("1/31/21")+0))+(FILTER('CC-Chase'!$F:$F,'CC-Chase'!$D:$D=("Grocery", 'CC-Chase'!$B:$B>=("1/1/21")+0,'CC-Chase'!$B:$B<=("1/31/21")+0)))

But this gives me an error. I’ve tried a few other combinations of this but always get an error.

Does anyone know what I’m doing wrong or have another way I could do this?

nt.number theory – Sum of inverse squares of numbers divisible only by primes in the kernel of a quadratic character

Let $chi$ be a primitive quadratic Dirichlet character of d modulus $m$, and consider the product
$$prod_{substack{p text{ prime} \ chi(p) = 1}} (1-p^{-2})^{-1}.$$

What can we say about the value of this product? Do we have good upper or lower bounds?

Some observations, ideas, and auxiliary questions

  • When $chi$ is trivial, it has value $zeta(2)$.
  • In general, since Chebotarev density theorem (CDT) tells us that $chi(p)$ is equidistributed in the limit, I would “want” the value to be something like

$$Big(zeta(2)prod_{p | m} (1-p^{-2})Big)^{frac{1}{2}}.$$

However, if I’m not mistaken, it seems that the error terms in effective forms of CDT may cause this to be very far from the truth. We can’t ignore what happens before we are close to equidistribution as the tail and the head are both $O(1)$. We can’t even control the error term well (without GRH) because of Siegel zeroes.

  • I don’t think we can appeal to Dirichlet density versions of CDT since those only tell us things in the limit as $s$ goes to $1$ and here $s = 2$.
  • Is there a way to “Dirichlet character”-ify a proof of $zeta(2) = pi^2/6$ to get a formula for this more general case? At least with Euler’s proof via Weierstrass factorization, it seems that we would need some holomorphic function which has zeroes whenever $chi(n) = 1$.

I had a few other ideas but they all seem to run into the same basic problem of “can’t ignore the stuff before the limit”… am I missing something?

chi squared – Sum of squares of normal distribution

I have a problem in which I need to apply weak law of large numbers to sum of squares of normal distribution with mean 1 and variance 1 .
X1 X2…~ N(1,1) They are iid

V(X1^2+X2^2+….)/n what will be the limit value of this when n tends to infinite.

My views:
I know sum of squares of SNV is chi square
Variance of this chi square will be 2n
But I don’t know if it follows central chi square in case when mean is not 0.

In this particular case will my variance be 2n.

Google Sheets formula IF or SUM with conditional formatting

Im looking for some much needed help after hours of looking.

I have lets call it a task list of things to do. Columns A B and C have text, column D is a number (hours it will take me to complete task) and column E is empty and use it to ‘tick’ off my list by typing ‘x’ with conditional formatting this in turn ‘greys out’ the whole row – and it also removes the number on this row from my chart, which is exactly what I want, but I also want my total values of column D to reflect this. I put my SUM in column F.

I want to SUM (or add together all values in column D) which is fine with a simple SUM formula, but I want sheets to minus any number that I have completed with my ‘x’ in column E. Im sure it must be possible as it does this automatically with any chart I add, but just want 1 cell with the answer?

FYI the conditional formatting I use is =$E10=”x” making the row grey if theres an ‘x’ there and I also use =ISBLANK(#REF!) if no ‘x’ is present

Any help is most appreciated

worksheet function – Excel: Finding the sum of a 3rd row by comparing rows that have 2 matching columns

I have a workbook where we have monthly phone bills broken down by line and funding codes. There’s over 200 lines on this file and growing. I need to find the sum of any rows that have the same cost center and program codes. A cost center may a different program code.

We take the original table and create another table streamlined for one line per unique pairing. This is where we want to have the sum of the original table (with matching criteria) show. There are over 50 cost centers and a dozen programs. So, I’m trying to find a tidy way to do this.

I’ve been experimenting with index, match, sumifs, and vlookup formulas. I haven’t had much luck, but I don’t have a lot of experience with them. I also debated whether a macro would work better, but I have even less experience with that.

Here is a sample file. I can’t post imbedded pictures yet, so I apologize for the link.

sample sheet image

sequences and series – Method to evaluate an infinite sum of ratio of Gamma functions (how does Mathematica do it?)

This question arose from Amdeberhan’s question, the evaluation of a double integral, which can be reduced to the evaluation of this series:
$$sum _{n=0}^{infty } frac{Gamma left(n+frac{1}{2}right)^2 Gamma left(n+frac{s}{2}right)}{Gamma (n+1)^2 Gamma (n+s)}=frac{pi ^2 2^{1-s} Gamma left(frac{s}{2}right)}{left(Gamma left(frac{3}{4}right) Gamma left(frac{s}{2} +frac{1}{4}right)right)^2},;;{rm Re},s>0.$$
The evaluation of the sum is Mathematica output. Can someone enlighten me as to how this calculation proceeds?

I went so far as to pay for Wolfram Alpha Pro, hoping that it would disclose the steps, but to no avail. What is even more frustrating is that for $s=1$ the right-hand-side is the square of a complete elliptic integral, which is also recognized immediately by Mathematica and was the original question in the cited post, so far without a conclusive answer.

google sheets – SUM all the Origin of Country

I need a formula that can add all the countries with that is in a column.

in Sheet ‘Export’ I have below countries of origin:
enter image description here

And in other sheet I need to add all the countries of origin and change the name from RO to Romania or SK to Slovakia, etc…

What I have until now is the formula: =TEXTJOIN(“, “,TRUE, IF(left(Export!Q1,2) = “RO” , “ROMANIA”, IF(left(Export!Q1,2) = “US” , “USA”, IF(left(Export!Q1,2) = “PT” , “PORTUGALIA”, IF(left(Export!Q1,2) = “FR” , “FRANTA”, IF(LEFT(Export!Q1,2) = “SK” , “SLOVACIA”))))))

But is not working. It only add the first country found, not all of them.

Can anyone help?

Thank you

linear algebra – How does the inverse of a sum of matrices change?

I have the following matrix

$ Omega (tau) equiv lim_{n to infty} sum_{i = 1}^n f_i (xi (tau)) x_i x_i^T $

where $ f_i $ is the probability density and $ xi _i $ is the quantile of the random variable $ Y_i $ conditioned on $ x_i $. If we for example assume that $ f_i $ is a normal density for all $ i $ or any other distribution for which the pdf decreases with $ tau $ closer to $ 0 $ or $ 1 $. The observations are independent but not necessarily identically distributed.

Is there a general property for how the inverse of said matrix would change if $ tau $ changes. In my specific case. Does the inverse decrease for extreme quantiles?

Algorithm to check that one sum is less than another

Suppose that we have two sums:

We can perform only two binary operations on operands of these sums:

  • lt (less than)
  • eq (equal)

These operations can return 3 possible results: true, false, unknown. Other operations like summation, subtraction and etc. are not allowed.

The question is how to implement algorithm for function lt(a1+a2+…+an, b1+b2+…+bm), that also returns true, false or unknown?

For example if we know that (n=m=3 && a1 < b3 && a2 = b2 && a3 < b1) the algorithm have to return true.
Or if we know that (n=3 && m=2 && a1 < b1 && a2 < b1 && a3 < b2 && b1 < b2) the algorithm have to return unknown.