Forum Discussion

Ben1022's avatar
Ben1022
Copper Contributor
Sep 28, 2020

Date range based Sum Up from multiple work sheet

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.

 

 

Formulas and Functions

6 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 28, 2020

    Ben1022 

    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))

    • Ben1022's avatar
      Ben1022
      Copper Contributor
      Sep 28, 2020
      Thanks Hans, I will try. Someone on FB gave me that idea, but it giving me false total. This was the formula =SUM(SUMIFS(INDIRECT(A1:A26&"!D3:D34"),INDIRECT(A1:A26&"!C3:C34"),">"&H11))
      I don't know how to upload a pic here, otherwise it was easy with ascreen shot.
      • JMB17's avatar
        JMB17
        Bronze Contributor
        Sep 28, 2020
        Did you sumif each sheet individually and compare to your manual calculation for each sheet to identify which worksheet(s) were causing the variance? It may be an issue with the data or your manual calculation is off.

        If you can upload the actual workbook, it would be more helpful than a screenshot.

Resources