1

enter image description here

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!

0

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

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.