Hoping some one will take a little time to explain this formula to me

[Tuckejam]

So im dealing with this formula

=IF($C2="","",SUMPRODUCT(COUNTIF(INDIRECT("'"&'Table of Contents'!$E$5:$E$41&"'!$A$1:IV1000"),$C2)))

now I know what it does I just don't understand how it does it

I know the first part =IF($C2="","", is just to clear a REF# error, IF there is no sku# in cell C2 then type nothing here in this cell otherwise do

SUMPRODUCT(COUNTIF(INDIRECT("'"&'Table of Contents'!$E$5:$E$41&"'!$A$1:IV1000"),$C2)))

so what i know is that this looks at the the Value in cell C2 and it counts how many times it exists on the worksheets that are listed in the range "E5:E41" of the "Table Of Contents" worksheet for the range shown A1:IV1000

so i have a couple of questions

I understand Sumproduct, and CountIF when they stand on their own how but i don't understand whats happening here when they are combined. and i kind of understand Indirect but not real well, and again only when used on its own

The Table of contents thing is really cool and had never head or seen of it before now but i don't understand how it works

( " ' " & 'Table of Contents'!$E$5:$E$41&"'!$A$1:IV1000")

like why do refer to the table of contents with open quote marks then and apostrophe and then close quote marks and then Ampersand.

Im hoping that someone will be able to break this all down for me and explain to me step by step what this formula is doing
and also a little more about the table of contents function and how to make i work with a list of named ranges.

I recently bought the book Excel 2010 Formulas in the hopes that some day soon i will be able to answer these questions on my own, and i love the book unfortunately there just are not enough hours in the day and im trying to get this figured out for work.

also if you know of any websites that kinda auto breakdown excel formula and explain what its doing step by step that to would be a awesome.

Thank you again for anyone with any input.

 

[XOR LX]

Hi,

Excel requires that references to sheet names which contain spaces are surrounded by single apostrophes. Though yours may not contain spaces, it is nevertheless good practice to include them in any formula which is constructing references to worksheets. For example, this simple formula, attempting to reference cell A1 on a tab called Master Sheet, would fail:

=Master Sheet!A1

whereas:

='Master Sheet'!A1

would be correct.

Had the tab name contained no spacing, e.g. MasterSheet, then:

=MasterSheet!A1

(without the apostrophes) would be fine.

INDIRECT simply tells Excel to interpret a string as an actual range reference, so:

INDIRECT("'"&'Table of Contents'!$E$5:$E$8&"'!$A$1:IV1000")

interprets the array of concatenated (the ampersand performs this job) strings:

{"'Sheet1'!$A$1:IV1000";"'Sheet2'!$A$1:IV1000";"'Sheet3'!$A$1:IV1000";"'Sheet4'!$A$1:IV1000";...}

etc., as actual range references, passes them all to the COUNTIFS, where they are evaluated and then summed by the SUMPRODUCT.

Hope that helps.

Regards

 

[mfexcel]

@XOR, very nice explanation


btw, you may find a similar usage of INDIRECT and &, together with other functions at:

Excel VLOOKUP Multiple Sheets

 

[Aladin Akyurek]

@XOR, very nice explanation


btw, you may find a similar usage of INDIRECT and &, together with other functions at:

Excel VLOOKUP Multiple Sheets

I have ever wanted to say the following about the web pages anybody can set up. What is disturbing about them is that they never cite/quote where they get their wisdom and leave unweary reader with the feeling that they are the author.

 

[mfexcel]

I apologize if I have created any confusion. That's absolutely not my intention.
I should be more conscious in quoting the source in the future.

I have ever wanted to say the following about the web pages anybody can set up. What is disturbing about them is that they never cite/quote where they get their wisdom and leave unweary reader with the feeling that they are the author.