Sum all sheet based on column header

  • hi guys,


    can some one help me? attached sample file. I am looking for formula which will sum based on month in b2 from all sheets. I have got formula which works fine for 1 sheet but can not make it work with all sheet.


    the original file has over 20 sheets which I am trying to add up. and has data over 5yrs.

  • Re: Sum all sheet based on column header


    Would a UDF be acceptable if nobody comes up with an Excel built-in function?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Sum all sheet based on column header


    With the sheets that are to be summed is row 2 always the header row and row 1 empty?


    Also are there always 10 rows of data?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Sum all sheet based on column header


    I also used SUMPRODUCT and INDIRECT but was also trying to make it dynamic for sheets and columns used in each sheet (not knowing if either of those would be constant) but couldn't get anything to work.


    This in the Worksheet Event Module for the "Sum" sheet is fully dynamic for both the above. Any change of date input in B2 will fire the code. Warnings for invalid entry in B2 is included (not a date entered or a date outside the data sheets date range).

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Sum all sheet based on column header


    Thanks Krishnakumar - exactly what I wanted.


    Thanks KJbox for the VBA but I think I will go with Krishnakumar solution.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!