Help to understand the following formula

**RagonichaFulva** · 02-22-2012, 08:26 AM

I have an excel file with the following formula:

Please Login or Register  to view this content.

I understand that we are using the sumifs function to sum different cells according to certain conditions. Nevertheless, I get confused with the "!" operators, and the use of lower case letters and the "'" symbols.

I learnt about the SUMIFS function, the INDIRECT function, etc. but I can't work out the meaning of this.

Maybe someone could change this formula in a "human-mode" version?

I would much appreciate your assistance.

**RagonichaFulva** · 02-22-2012, 08:31 AM

Ok. I have re-read it and I understand it.

It actually calls values from another sheet.

Now what I ignore is why in the following part (for instance):

INDIRECT("'"&$J$3&"'!o7:o"&$K$3),

it uses lower case.

**arlu1201** · 02-22-2012, 02:44 PM

In the case of cell references, cases dont matter.

**daddylonglegs** · 02-22-2012, 03:37 PM

For better understanding I prefer to show cell references as upper case at all times, if you use a reference in lower case Excel converts it anyway, e.g. type in

=a1

and excel will convert that to upper case

.....but inside INDIRECT "'!k7:k" is simply a text string, not a reference so Excel doesn't convert it within that formula

**RagonichaFulva** · 02-22-2012, 04:31 PM

Originally Posted by daddylonglegs

.....but inside INDIRECT "'!k7:k" is simply a text string, not a reference so Excel doesn't convert it within that formula

Thanks daddylonglegs. Actually, that was what I observed but I didn't figure out that excel treated the reference as a string.

Now everything makes sense and the world is a fabulous place.

Thanks to arlu1201 for his explanation, too.