sum ifs, indirects, confusion

fable

New Member
Joined
Nov 16, 2012
Messages
24
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,
 
Hi
Why do you need SUMPRODUCT and INDIRECT? Shouldn't this work?
=SUMIF('November:Initial Lease'!B:B,B6,'November:Initial Lease'!F:F)
 
Upvote 0
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
 
Upvote 0
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")))
 
Upvote 0
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.

Robert Mika said:
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!
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top