1

Assuming I insert every day some rows into Sheet1, this formula stops working and I have to manually edit every day --- can the reference to Cell A2 and Cell E2 be a variable that remains hardcoded as for example inserting 4 rows both became A6 and E6 and the value totals are all wrong until I manually change to A2 and E2 from A6 and E6:

=ArrayFormula(QUERY(FILTER({WEEKDAY(Sheet1!A2:A),Sheet1!E2:E},ISNUMBER(Sheet1!E2:E),Sheet1!E2:E>0),"SELECT Col1, COUNT(Col1), SUM(Col2) GROUP BY Col1 ORDER BY Col1 ASC LABEL Col1 'Day', COUNT(Col1) 'Count by Day', SUM(Col2) 'Sum by Day'"))

Can A2 be some pointer to a cell that has the text "A2" and it always stays as A2 in the formula -- same question for E2?

Improve this question

1 Answer 1

Reset to default
2

You mentioned:

Can A2 be some pointer to a cell that has the text "A2" and it always stays as A2 in the formula -- same question for E2 ?

You do not need to reference a pointer to an extra cell.

What you are looking for is the INDIRECT function.

You can replace your ranges
Sheet1!A2:A and Sheet1!E2:E with
INDIRECT("Sheet1!A2"&":A") and INDIRECT("Sheet1!E2"&":E")

Improve this answer
4
  • This gives a FORMULA PARSE ERROR .. =ArrayFormula(QUERY(FILTER({WEEKDAY(INDIRECT("Sheet1!A2"&":A")), INDIRECT("Sheet1!E2"&":E"), ISNUMBER(INDIRECT("Sheet1!E2"&":E")), INDIRECT("Sheet1!E2"&":E")>0), "SELECT Col1, COUNT(Col1), SUM(Col2) GROUP BY Col1 ORDER BY Col1 ASC LABEL Col1 'Day', COUNT(Col1) 'Count by Day', SUM(Col2) 'Sum by Day' format Col1 'dddd' "))
    – rosa
    Commented Feb 7, 2021 at 1:31
  • You forgot a } before the ISNUMBER. Your formula should be =ArrayFormula(QUERY(FILTER({WEEKDAY(INDIRECT("Sheet1!A2"&":A")),INDIRECT("Sheet1!E2"&":E")},ISNUMBER(INDIRECT("Sheet1!E2"&":E")),INDIRECT("Sheet1!E2"&":E")>0),"SELECT Col1, COUNT(Col1), SUM(Col2) where Col1 is not null GROUP BY Col1 ORDER BY Col1 ASC LABEL Col1 'Day', COUNT(Col1) 'Count by Day', SUM(Col2) 'Sum by Day' format Col1 'dddd'"))
    – marikamitsos
    Commented Feb 7, 2021 at 1:36
  • 1
    Perfect. Thank you
    – rosa
    Commented Feb 7, 2021 at 1:48
  • Thank you for taking the time to accept. I had a look at your test sheet. Out of curiosity. Why is column Sheet1!A formatted as Plain text and not as Date time as it should be?
    – marikamitsos
    Commented Feb 7, 2021 at 2:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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