Which formula is this" {sum()*()*()}"

[XS]

I need to use the formula like

{=SUM(($B14='Namedata'!$M$3:$M$14707)*(W$5='Namedata'!$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.

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Luke M]

I think you're wanting SUMPRODUCT

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

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

 

[XS]

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!

 

[Chip Pearson]

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)

 

[Gord Dibben]

It is called an "array formula"


Gord Dibben MS Excel MVP