1

I'm trying to do a form autocomplete formula like "Ben Collin's Tutorial" that adds more formulas down a column as values are added in an adjacent column. This works with if functions, sum, basic arithmetic etc. but with sumifs, the first value is repeated. There have been attempted solutions on stackoverflow before, but it relies on the keys to be as long as the display or it uses queries so that the columns have to be adjacent or well behaved. Really, the vocabulary and labels are sufficient or consistent enough to describe the problem with an example so I made one on google sheets. shareable sheets link

The formula in e2 is:

=arrayformula(if($A2:$A<>"",sumifs(data!C:C,data!A:A,$A2:$A,data!B:B,"k"),""))

It would be nice if the results were the results of column f. I don't know what is going on in the background so that this would ever be a desired results, but I also tried this, and it failed.

=arrayformula(if(A2:A<>"",sumifs(data!C:C,data!A:A,A2:A,data!B:B,"k"),""))

or

=arrayformula(if(A$2:A<>"",sumifs(data!C:C,data!A:A,A$2:A,data!B:B,"k"),""))

Suffice it to say, the $ don't work here the way they work with conditional formatting rules, which is a pity.

player0
  • 124,011
  • 12
  • 67
  • 124
Henrietta Martingale
  • 771
  • 8
  • 27

1 Answers1

0

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, QUERY(data!A2:C, 
 "select A,sum(C) where B = 'k' group by A"), 2, 0)))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Is there a general list of formulas that work with arrayformula? I know sum does, the series of if's do. I guess vlookup does. I guess sumifs doesn't. Anyway, thanks! Do you have something that does two criteria like for a2:a and b2:b. Just in case for the future. – Henrietta Martingale May 26 '20 at 14:37
  • there is no list to my knowledge. for A and B column you can just join it like A2:A&B2:B. VLOOKUP is supported as well as IF but not IFS nor SUM. to sum arrays you need to use either QUERY or MMULT. the most common assumption is that AND and OR are supported in ARRAYFORMULA but they arent. you will need to use + and * for that – player0 May 26 '20 at 16:18
  • So the basic thing I took from this is you can vlookup a query. Thus summing works. I wonder if concat works to handle multiple keys and thus artificially create a sumifs? Also, if this is faster than the udf I made – Henrietta Martingale May 28 '20 at 20:35