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!!!

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

"mmatz" wrote:

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!!!

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?

"Mike H" wrote:

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

"mmatz" wrote:

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!!!

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


"mmatz" wrote in message
...
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?

"Mike H" wrote:

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

"mmatz" wrote:

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!!!