what does this forumla mean?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mmatz - 08 May 2008 17:36 GMT

I got this forumla:
=SUM(IF(('[Title and Reg Chat Data.xls]FY08'!$C$2:$C$9000=W$66)*('[Title and
Reg Chat Data.xls]FY08'!$E$2:$E$9000=$C67),'[Title and Reg Chat
Data.xls]FY08'!$A$2:$A$9000,0))

I understand it is going to the worksheet title and reg, to worksheet fy08,
looking at the range c2:c9000- what is the = w66? As well as the = c67? I
know it is an if asking if those ranges = those cells, but what is the true
part? and how does sum fit in?

Thanks!!!

Reply to this Message

Mike H - 08 May 2008 17:56 GMT

Hi,

In the FY08 sheet it is trying to match a value in W66 in the C column range
and the value in C67 in the E column range and if a match is found it's
summing the corresponding value in the A column Range.

Mike

> I got this forumla:
> =SUM(IF(('[Title and Reg Chat Data.xls]FY08'!$C$2:$C$9000=W$66)*('[Title and
[quoted text clipped - 7 lines]
>
> Thanks!!!

Reply to this Message

mmatz - 08 May 2008 18:20 GMT

Thank you Mike but I am an idiot

So it says IF(('[Title and Reg Chat Data.xls]FY08'!$C$2:$C$9000=W$66
meaning it is adding the range c2:c9000? and let it know if they = W66

Then multiply that with )*('[Title and

> > Reg Chat Data.xls]FY08'!$E$2:$E$9000=$C67) and let it know if they = c67

Then if true [Title and Reg Chat

> > Data.xls]FY08'!$A$2:$A$9000

Otherwise return 0

What is the sum?

> Hi,
>
[quoted text clipped - 15 lines]
> >
> > Thanks!!!

Reply to this Message

Rick Rothstein (MVP - VB) - 08 May 2008 19:06 GMT

This is from the remarks section for the help file on SUM...

"If an argument is an array or reference, only numbers in that array
or reference are counted. Empty cells, logical values, text, or error
values in the array or reference are ignored."

The key part of the above is where it says the SUM function can process an
array. $C$2:$C$9000, $E$2:$E$9000 and $A$2:$A$9000 are arrays (they each
span the same number of cells) and so the SUM function forces the expression
containing them to be evaluated one cell (from each array) at a time and
then adds up the results of each of those individual evaluated expressions.
In this case, the expression containing the array is the IF function, so the
SUM function is adding up all the individual evaluations of the IF function
for each cell, one at a time, in the array. Hence, what are being summed are
these individual formulas...

SUM(IF(('[Title and Reg Chat Data.xls]FY08'!C2=W66)*
('[Title and Reg Chat Data.xls]FY08'!E2=C67),
'[Title and Reg Chat Data.xls]FY08'!A2,0))

=SUM(IF(('[Title and Reg Chat Data.xls]FY08'!C3=W66)*
('[Title and Reg Chat Data.xls]FY08'!E3=C67),
'[Title and Reg Chat Data.xls]FY08'!A3,0))

=SUM(IF(('[Title and Reg Chat Data.xls]FY08'!C4=W66)*
('[Title and Reg Chat Data.xls]FY08'!E4=C67),
'[Title and Reg Chat Data.xls]FY08'!A4,0))

etc. where the cells on '[Title and Reg Chat Data.xls]FY08' are being
incremented throughout the range of the array.

Rick

> Thank you Mike but I am an idiot
>
[quoted text clipped - 36 lines]
>> >
>> > Thanks!!!

Reply to this Message