0

Can you help please? I have worked out how to get the result I need, but how do I make them work with arrayformula? Here is my google sheet

This works for me in Y2:

=COUNT(QUERY('Feed from Register'!$B$2:$E, "select B where B > date '"&TEXT(TODAY()-ValidDatesOnRegister!$B$1,"yyyy-mm-dd")&"' and E contains '"&$W2&"' "))

But, however much I've tried, I can't make it work as an arrayformula down the column. If it's the and that's causing the problem, what approach do I need to take? IF or filter?

Help much appreciated... enter image description here

Improve this question
0

It's not possible to make work your formula as an array formula so your alternative is to change the way that your calculation is done but perhaps the easier solution is just to do a fill down of your current formula.

Improve this answer
1
  • I know I could copy the formula down but very inconvenient to do as new rows will be automatically added from clients using google forms. Surely, there must be a different approach I can use with IF and FILTER perhaps still in combination with QUERY?
    – user3379057
    Feb 27 '20 at 18:03
0

You must have figured it out by now. (good work).

Still. After having a look at your spreadsheet, I would suggest you change your formula in column X to 

=ArrayFormula(IF($W2:W="","",COUNTIF('Feed from Register'!$E$2:$E,"*"&W2:$W&"*")))

The change will cut away the 0s going all the way down to the last row and will improve both the readability as well as the functionality of your sheet.

Improve this answer
2
  • If still in trouble please let us know.
    – marikamitsos
    Mar 2 '20 at 0:34
  • Thanks @marikamitsos, yes I did sort out formula for column X. It's the Y formula that I'm struggling with converting to arrayformula. This works well if I copy and paste: =IF(LEN(W2:W),IF($X2=0,1,COUNT(QUERY('Feed from Register'!$B$2:$E, "select B where B > date '"&TEXT(TODAY()-ValidDatesOnRegister!$B$1,"yyyy-mm-dd")&"' and E contains '"&$W2&"' "))),"") This formula looks at sheet Feed from Register and checks how many times the attendee has attended in the last x days (x being set in sheet ValidDatesOnRegister, currently set at 180 days). What's the best approach?
    – user3379057
    Mar 2 '20 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.