Making dynamic number of arguments based on a specific criteria for importrange function

Question

I am counting number of cases per day in each month by importrange data from daily sheets in every month with this formula on D3 for example:

=SUM(IMPORTRANGE($Q$5,R5),IMPORTRANGE($Q$6,R5),IMPORTRANGE($Q$7,R5),IMPORTRANGE($Q$8,R5),IMPORTRANGE($Q$9,R5),IMPORTRANGE($Q$10,R5),IMPORTRANGE($Q$11,R5),IMPORTRANGE($Q$12,R5),IMPORTRANGE($Q$13,R5),IMPORTRANGE($Q$14,R5),IMPORTRANGE($Q$15,R5),IMPORTRANGE($Q$16,R5),IMPORTRANGE($Q$17,R5),IMPORTRANGE($Q$18,R5),IMPORTRANGE($Q$19,R5),IMPORTRANGE($Q$20,R5),IMPORTRANGE($Q$21,R5),IMPORTRANGE($Q$22,R5),IMPORTRANGE($Q$23,R5),IMPORTRANGE($Q$24,R5),IMPORTRANGE($Q$25,R5),IMPORTRANGE($Q$26,R5),IMPORTRANGE($Q$27,R5),IMPORTRANGE($Q$28,R5),IMPORTRANGE($Q$29,R5),IMPORTRANGE($Q$30,R5),IMPORTRANGE($Q$31,R5),IMPORTRANGE($Q$32,R5))

Rows from Q5:Q32 (28 rows) represent IDs of daily sheets for the month of February (C1) & Rows R5:R36 are cells that contain the numbers to be summed. Those cells R5:R36 are fixed in all daily sheets. For another month like January, the number of rows will be 31 (Q5:Q35) where I need to aextend the previous formula to : Importrange(Q33,R5),Importrange(Q34,R5),Importrange(Q35,R5)) . I am looking for an idea on how to make the formula fixed so I don't need to change the number of arguments based on number of rows (which represent daily sheet IDs). It makes sense for me so that I can make this sheet a master sheet where the data will change whenever the text in C1 is changed to a different month otherwise the formula will show error message if the number of ID rows are less than the number of arguments in the formula (for example, importrange (Q5,R5),.... importrange(Q35:R5) for February where the IDs ends on row R32).

I thought on using sumif() using criteria like if C1=Januaray or March or May or any month contains 31 days then 30 days then 28 days!

Answer 1

I could Use this trick on the counter cells =ifs(OR(ActiveMonth="January",ActiveMonth="March",ActiveMonth="May",ActiveMonth="July",ActiveMonth="August",ActiveMonth="October",ActiveMonth="December"),Y1,OR(ActiveMonth="April",ActiveMonth="June",ActiveMonth="September",ActiveMonth="November"),Z1,OR(ActiveMonth="February"),AA1), where I move down one value of cells (Y1,Y2..etc), (Z1,Z2.. etc)

For Column Y, this formula on 35 rows: =SUM(IMPORTRANGE($Q$5,R5),IMPORTRANGE($Q$6,R5),IMPORTRANGE($Q$7,R5),IMPORTRANGE($Q$8,R5),IMPORTRANGE($Q$9,R5),IMPORTRANGE($Q$10,R5),IMPORTRANGE($Q$11,R5),IMPORTRANGE($Q$12,R5),IMPORTRANGE($Q$13,R5),IMPORTRANGE($Q$14,R5),IMPORTRANGE($Q$15,R5),IMPORTRANGE($Q$16,R5),IMPORTRANGE($Q$17,R5),IMPORTRANGE($Q$18,R5),IMPORTRANGE($Q$19,R5),IMPORTRANGE($Q$20,R5),IMPORTRANGE($Q$21,R5),IMPORTRANGE($Q$22,R5),IMPORTRANGE($Q$23,R5),IMPORTRANGE($Q$24,R5),IMPORTRANGE($Q$25,R5),IMPORTRANGE($Q$26,R5),IMPORTRANGE($Q$27,R5),IMPORTRANGE($Q$28,R5),IMPORTRANGE($Q$29,R5),IMPORTRANGE($Q$30,R5),IMPORTRANGE($Q$31,R5),IMPORTRANGE($Q$32,R5),IMPORTRANGE($Q$33,R5),IMPORTRANGE($Q$34,R5),IMPORTRANGE($Q$35,R5))

On Column Z, this formula on 35 rows: =SUM(IMPORTRANGE($Q$5,R5),IMPORTRANGE($Q$6,R5),IMPORTRANGE($Q$7,R5),IMPORTRANGE($Q$8,R5),IMPORTRANGE($Q$9,R5),IMPORTRANGE($Q$10,R5),IMPORTRANGE($Q$11,R5),IMPORTRANGE($Q$12,R5),IMPORTRANGE($Q$13,R5),IMPORTRANGE($Q$14,R5),IMPORTRANGE($Q$15,R5),IMPORTRANGE($Q$16,R5),IMPORTRANGE($Q$17,R5),IMPORTRANGE($Q$18,R5),IMPORTRANGE($Q$19,R5),IMPORTRANGE($Q$20,R5),IMPORTRANGE($Q$21,R5),IMPORTRANGE($Q$22,R5),IMPORTRANGE($Q$23,R5),IMPORTRANGE($Q$24,R5),IMPORTRANGE($Q$25,R5),IMPORTRANGE($Q$26,R5),IMPORTRANGE($Q$27,R5),IMPORTRANGE($Q$28,R5),IMPORTRANGE($Q$29,R5),IMPORTRANGE($Q$30,R5),IMPORTRANGE($Q$31,R5),IMPORTRANGE($Q$32,R5),IMPORTRANGE($Q$33,R5),IMPORTRANGE($Q$34,R5))

On Column AA, this formula of 35 rows: =SUM(IMPORTRANGE($Q$5,R5),IMPORTRANGE($Q$6,R5),IMPORTRANGE($Q$7,R5),IMPORTRANGE($Q$8,R5),IMPORTRANGE($Q$9,R5),IMPORTRANGE($Q$10,R5),IMPORTRANGE($Q$11,R5),IMPORTRANGE($Q$12,R5),IMPORTRANGE($Q$13,R5),IMPORTRANGE($Q$14,R5),IMPORTRANGE($Q$15,R5),IMPORTRANGE($Q$16,R5),IMPORTRANGE($Q$17,R5),IMPORTRANGE($Q$18,R5),IMPORTRANGE($Q$19,R5),IMPORTRANGE($Q$20,R5),IMPORTRANGE($Q$21,R5),IMPORTRANGE($Q$22,R5),IMPORTRANGE($Q$23,R5),IMPORTRANGE($Q$24,R5),IMPORTRANGE($Q$25,R5),IMPORTRANGE($Q$26,R5),IMPORTRANGE($Q$27,R5),IMPORTRANGE($Q$28,R5),IMPORTRANGE($Q$29,R5),IMPORTRANGE($Q$30,R5),IMPORTRANGE($Q$31,R5),IMPORTRANGE($Q$32,R5))

Column Y sums values across 31 sheets Column Z sums values across 30 sheets Column Z sums values across 28 sheets