SUMIF MultipleSheets

[surkdidat]

Hi
I am trying to add up all occurrences in cell G2 across multiple sheets where F2 contains "4303". I have entered a "Start" tab and "End" Tab, between the relevant sheets, if this helps

 

[steve the fish]

Put the sheet names into a range eg A1:A5 then

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A5&"'!G2"),4303,INDIRECT("'"&A1:A5&"'!G2")))

 

[surkdidat]

Hi this is bring up #REF ! - in the sheets I am referencing F2 contains "4303" and G2 contains what I want to count.

Put the sheet names into a range eg A1:A5 then

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A5&"'!G2"),4303,INDIRECT("'"&A1:A5&"'!G2")))

 

[surkdidat]

Replying to my own post - its actually a date on the tab, so I need to add a bit that is "DD MM YYYY" in there" - I believe...... Any help appreciated

Hi this is bring up #REF ! - in the sheets I am referencing F2 contains "4303" and G2 contains what I want to count.

 

[steve the fish]

Put the sheet names in A1:A5 then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A5&"'!F2"),4303,INDIRECT("'"&A1:A5&"'!G2")))

If you only have 4 sheets for example then use A1:A4 or maybe use a named range. I dont know what you mean by its a date on the tab??

 

[surkdidat]

There are 52 tabs, one for each week commencing ie 31 December 2018, 7 January 2019 etc. I have put the names in a range, but it brings up an error. If I name them "XXX" and "YYY" for example, the formula works perfectly. I seem to remember with INDIRECT that if it is a date, you need to put something in the formula that is "DD MMMM YYY" so it converts it from a number?

Put the sheet names in A1:A5 then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A5&"'!F2"),4303,INDIRECT("'"&A1:A5&"'!G2")))

If you only have 4 sheets for example then use A1:A4 or maybe use a named range. I dont know what you mean by its a date on the tab??

 

[steve the fish]

Oh i see. It will just be excel converting your dates to true dates as you put them into A1:A5. Just format those cells to text then input the sheet names.

 

[surkdidat]

Thanks for your help - finding an old formula I have put the two together and come up with

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(V7:V43,"d mmmm yyyy")&"'!"&"F2"),"*4303*",INDIRECT("'"&TEXT(V7:V43,"d mmmm yyyy")&"'!"&"G2")))

which seems to work.

Oh i see. It will just be excel converting your dates to true dates as you put them into A1:A5. Just format those cells to text then input the sheet names.