Sep 28 2020 12:35 PM - edited Sep 28 2020 12:39 PM
Hi,
I have a work book with 26 sheets., where Jumbo1 is sheet one and Boeing9 is sheet 26.
All 26 sheets are with identical column and data.
I want a formula, to add up amounts collected, for a month from the date I enter in H11.
So, Input is- start date into H11. I want to get a return value, which is total amount collected from all 26 sheets, for one month, from the date I enter. Hope it make sense.
I use this formula, =SUMIFS(D3:D34,C3:C34,">"&H11) which work on one sheet.
I tried this =SUMIFS('Jumbo1:Boing9'!D3:D34,'Jumbo1:Boeing9'!C3:C34,">"&H11)but giving me #value error.
Can anyone help me to correct it or with a new one.
Thanks in advance.
Sep 28 2020 12:54 PM
Create a list of the sheet names Jumbo1 ... Boeing9 in a column on the same sheet as the formula, say in N1:N26. You can then use
=SUMPRODUCT(SUMIFS(INDIRECT("'"&N1:N26&"'!D3:D34"),INDIRECT("'"&N1:N26&"'!C3:C34"),">"&H11))
Sep 28 2020 01:09 PM
Sep 28 2020 01:12 PM
Save your screenshot to disk, then drag the picture file from File Explorer / Finder into the reply box here.
Sep 28 2020 03:10 PM
Sep 28 2020 11:18 PM
@Hans Vogelaar This is my practice sheet. So the sheet names are different, but the same in effect.
Sep 28 2020 11:19 PM