What does this mean?

[wildturkey]

HI, I've been using this formula for some time and I can just about fudge the way it works.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Courses&"'!b:b"),$B4,INDIRECT("'"&Courses&"'!"&SUBSTITUTE(SUBSTITUTE(REPT(ADDRESS(1,COLUMNS($A:G),4),2),1,":",1),1,""))))

I understand its looking at the named range 'Courses' and picking up individual sheet names there, and if they exist adding up their values based on cell $B4, but could someone please explain the SUBSTITUTE(SUBSTITUTE(REPT(ADDRESS(1,COLUMNS($A:G),4),2),1,":",1),1,"")))) element, especially how the $A:G part works and whether the following digits have any impact on the column that gets summed - or is that purely down to the £A: G element?

Many thanks

 

[steve the fish]

Im struggling to see the point of it to be honest. Just produces G:G. Not sure why you wouldnt just type G:G.

 

[steve the fish]

Sorry i wasnt looking at the correct context. I just looked at the formula rather than in conjunction with the indirect.

 

[steve the fish]

So breaking down the formula:

=SUBSTITUTE(SUBSTITUTE(REPT(ADDRESS(1,COLUMNS($A:G),4),2),1,":",1),1,"")

First calculate the COLUMNS($A:G) which produces 7. Formula now is:

=SUBSTITUTE(SUBSTITUTE(REPT(ADDRESS(1,7,4),2),1,":",1),1,"")

Then the ADDRESS part, ADDRESS(1,7,4), produces G1. Our formula now is:

=SUBSTITUTE(SUBSTITUTE(REPT("G1",2),1,":",1),1,"")

Now REPT("G1",2) produces G1G1. Formula now is:

=SUBSTITUTE(SUBSTITUTE("G1G1",1,":",1),1,"")

Now the 2nd SUBSTITUTE you see above is SUBSTITUTE("G1G1",1,":",1). This produces G:G1. We now have:

=SUBSTITUTE("G:G1",1,"")

Which resolves to G:G.

 

[wildturkey]

Many thanks, will start trying to work that through and get it clear in my head, but that is excellent thank you!!