yogi_From A Table Of First Name Last Name And Amount Paid Compute First_And_Last_Name And Life_Time_Value

                 Google Spreadsheet   Post  #1821

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-03-2014

post by  Mika H.:

(https://productforums.google.com/forum/#!mydiscussions/docs/G4SNeeWoCWs)
Sumifs in an array

Hello, all. I have a webform that automatically fills out a spreadsheet when it's submitted. One new row gets added to the sheet. 

In A, I have the customer's first name. In B, their last name. In column C, I have the amount they paid. 

In D, I have this formula:=ArrayFormula($A$2:A&" "&$B$2:B)

Column D now reads as first and last name put together. Since it's an array, it also fills down indefinitely, and keeps filling the cells with the first and last name combo even when I add more cells, or when the form adds more cells. It's perfect. 

What I'd like to do now, is to have a lifetime amount column that behaves the same way. For example:

In column E, I have this formula:=if(a2:a>0,sumifs(C:C,A:A,"="&$A$2:A),"")

Now this works to give me the numbers that I need, but only if I fill it down for the whole chart. NBD in the example chart, where I have 12 entries, but in the real chart, I have roughly 12,000 entries, and more are constantly being added. What I would like to do is to have an infinitely filling array formula like I do in column D. 

I tried this: =ArrayFormula(if(a2:a>0,sumifs(C:C,A:A,"="&$A$2:A),""))

but that just gives me the result for the first entry in every cell for the rest of the chart. 

Can anyone tell me what I'm doing wrong or how to fix this formula?

An example sheet is here:

https://docs.google.com/a/internetreputation.com/spreadsheets/d/14WYTLZKCcybendjQIZ4jf1TiymHM9TwEvmkGzasVvsU/edit?usp=sharing

Thank you so much!

-----------------------------------------------------------------------------------------------------------------------------------