I need to use the formula like

{=SUM(($B14='Namedata'!$M$3:$M$14707)*(W$5='Nameda ta'!$AC$3:$AC$14707)*('Namedata'!$B$3:$B$14707))}

What's the name of it?

It seems it reference some cell from other sheet to check whether the
criterias are fulfilled, if then SUM them?

Thanks.

If Col M = B14 and If ColAC = W5 then add Col B

If this post helps click Yes
---------------
Jacob Skaria


"XS" wrote:

I need to use the formula like

{=SUM(($B14='Namedata'!$M$3:$M$14707)*(W$5='Nameda ta'!$AC$3:$AC$14707)*('Namedata'!$B$3:$B$14707))}

What's the name of it?

It seems it reference some cell from other sheet to check whether the
criterias are fulfilled, if then SUM them?

Thanks.

Is there a name for this fomula?

And how can I compute? It seems "enter" doesnt work. I need to press
"shift+ctrl+enter", right? But sometime I get zero, which clearly should not
be zero.....

Thanks very much!

"Jacob Skaria" wrote:

If Col M = B14 and If ColAC = W5 then add Col B

If this post helps click Yes
---------------
Jacob Skaria


"XS" wrote:

I need to use the formula like

{=SUM(($B14='Namedata'!$M$3:$M$14707)*(W$5='Nameda ta'!$AC$3:$AC$14707)*('Namedata'!$B$3:$B$14707))}

What's the name of it?

It seems it reference some cell from other sheet to check whether the
criterias are fulfilled, if then SUM them?

Thanks.

It is called an "array formula"


Gord Dibben MS Excel MVP

On Wed, 8 Jul 2009 10:09:01 -0700, XS wrote:

Is there a name for this fomula?

And how can I compute? It seems "enter" doesnt work. I need to press
"shift+ctrl+enter", right? But sometime I get zero, which clearly should not
be zero.....

Thanks very much!

"Jacob Skaria" wrote:

If Col M = B14 and If ColAC = W5 then add Col B

If this post helps click Yes
---------------
Jacob Skaria


"XS" wrote:

I need to use the formula like

{=SUM(($B14='Namedata'!$M$3:$M$14707)*(W$5='Nameda ta'!$AC$3:$AC$14707)*('Namedata'!$B$3:$B$14707))}

What's the name of it?

It seems it reference some cell from other sheet to check whether the
criterias are fulfilled, if then SUM them?

Thanks.

I think you're wanting SUMPRODUCT

=SUMPRODUCT(($B14='Namedata'!$M$3:$M$14707)*(W$5=' Namedata'!$AC$3:$AC$14707)*('Namedata'!$B$3:$B$147 07))

And your analysis is correct, its a way to have more than 1 cirteria and do
a sum based on those crtieria.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"XS" wrote:

I need to use the formula like

{=SUM(($B14='Namedata'!$M$3:$M$14707)*(W$5='Nameda ta'!$AC$3:$AC$14707)*('Namedata'!$B$3:$B$14707))}

What's the name of it?

It seems it reference some cell from other sheet to check whether the
criterias are fulfilled, if then SUM them?

Thanks.

It is an Array Formula. See
http://www.cpearson.com/excel/ArrayFormulas.aspx
for tons of info about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 8 Jul 2009 09:04:01 -0700, XS
wrote:

I need to use the formula like

{=SUM(($B14='Namedata'!$M$3:$M$14707)*(W$5='Named ata'!$AC$3:$AC$14707)*('Namedata'!$B$3:$B$14707))}

What's the name of it?

It seems it reference some cell from other sheet to check whether the
criterias are fulfilled, if then SUM them?

Thanks.