sum ifs, indirects, confusion

[fable]

Hey, have been reading this thread, to try and get some clarity, but it's not heling me. Whilst I understand Sumifs, and sumproducts, the indirect part of this is throwing me just a little.

In essence, what I'm looking to do, if to find the contents of a cell on the main spreadsheet, to find it all on all the spreadsheets from November to the Initial Lease. The cell it's looking for will be in the B column, the value it should be summing across the the different sheets is in column F.

This is what I'm attempting to use:

=SUMPRODUCT(SUMIF(INDIRECT("'"&November:Initial Lease&"'!B:B"),B6,INDIRECT("'"&November:Initial Lease&"'!F:F")))

And then i get a #name error, when, as fas as I can see, the names of the sheets involved are right?

If someone could give me some help on this I'd be grateful!

Thanks in advance,

 

[Vidar]

Hi
Why do you need SUMPRODUCT and INDIRECT? Shouldn't this work?
=SUMIF('November:Initial Lease'!B:B,B6,'November:Initial Lease'!F:F)

 

[Robert Mika]

Hey, have been reading this thread, to try and get some clarity, but it's not heling me. Whilst I understand Sumifs, and sumproducts, the indirect part of this is throwing me just a little.

In essence, what I'm looking to do, if to find the contents of a cell on the main spreadsheet, to find it all on all the spreadsheets from November to the Initial Lease. The cell it's looking for will be in the B column, the value it should be summing across the the different sheets is in column F.

This is what I'm attempting to use:

=SUMPRODUCT(SUMIF(INDIRECT("'"&November:Initial Lease&"'!B:B"),B6,INDIRECT("'"&November:Initial Lease&"'!F:F")))

And then i get a #name error, when, as fas as I can see, the names of the sheets involved are right?

If someone could give me some help on this I'd be grateful!

Thanks in advance,

Hello and welcome to MrExcel.
In the example from the topic you have mentioned the range $Z$1:$Z$212 is a list of the sheets in your case
November
.
.
.
Initial Lease

 

[Aladin Akyurek]

Hey, have been reading this thread, to try and get some clarity, but it's not heling me. Whilst I understand Sumifs, and sumproducts, the indirect part of this is throwing me just a little.

In essence, what I'm looking to do, if to find the contents of a cell on the main spreadsheet, to find it all on all the spreadsheets from November to the Initial Lease. The cell it's looking for will be in the B column, the value it should be summing across the the different sheets is in column F.

This is what I'm attempting to use:

=SUMPRODUCT(SUMIF(INDIRECT("'"&November:Initial Lease&"'!B:B"),B6,INDIRECT("'"&November:Initial Lease&"'!F:F")))

And then i get a #name error, when, as fas as I can see, the names of the sheets involved are right?

If someone could give me some help on this I'd be grateful!

Thanks in advance,

Create first a range housing the names of the relevant sheets, select that range, name it say SheetList using the Name Box on the Formula Bar. Now we can invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B:B"),B6,INDIRECT("'"&SheetList&"'!F:F")))

 

[fable]

Hi
Why do you need SUMPRODUCT and INDIRECT? Shouldn't this work?
=SUMIF('November:Initial Lease'!B:B,B6,'November:Initial Lease'!F:F)

Unfortunately not, to my knowledge, sum if wont work across several sheets.

Hello and welcome to MrExcel.
In the example from the topic you have mentioned the range $Z$1:$Z$212 is a list of the sheets in your case
November
.
.
.
Initial Lease

Thanks for the avice, but yes, I understand that, and that's what I've tried to replicate in the formula in the first post. It hasn't worked, so, hence the questioning.

Create first a range housing the names of the relevant sheets, select that range, name it say SheetList using the Name Box on the Formula Bar. Now we can invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B:B"),B6,INDIRECT("'"&SheetList&"'!F:F")))

Thanks very much! Works a treat I do believe!! I did wonder if the spaces in the tab titles was killing it along the way!

Thanks again!