Curious about SUMPRODUCT
 

Given the following array:

1024 20
100 30
600 20
700 10
800 10

Is there a COUNTIF equivalent to the following formula?
=SUMPRODUCT(--(A4:A8200),--(A4:A8/B4:B8=30))

I am just starting to appreciate the power of SUMPRODUCT, so I want to try
and understand limitations of it as well as replacement functions.

Insofar as SUMPRODUCT will compute with elements from 2 arrays at once,
there is no equivilant COUNTIF (maybe unless it's teamed up with other
functions, but I wouldn't want to try it).

There are SUMPRODUCT statements that use the + operator (translates to "or"
in english) where 2 COUNTIFs can be used and summed together, though.


"Wazooli" wrote in message
...
Given the following array:

1024 20
100 30
600 20
700 10
800 10

Is there a COUNTIF equivalent to the following formula?
=SUMPRODUCT(--(A4:A8200),--(A4:A8/B4:B8=30))

I am just starting to appreciate the power of SUMPRODUCT, so I want to try
and understand limitations of it as well as replacement functions.

COUNTIF has only one criterion (notwithstanding that Help calls the last
parameter "criteria" in the plural) so there is equivalent to your formula.

best wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Wazooli" wrote in message
...
Given the following array:

1024 20
100 30
600 20
700 10
800 10

Is there a COUNTIF equivalent to the following formula?
=SUMPRODUCT(--(A4:A8200),--(A4:A8/B4:B8=30))

I am just starting to appreciate the power of SUMPRODUCT, so I want to try
and understand limitations of it as well as replacement functions.

Thank you gentlemen. Much appreciated.

"Bernard Liengme" wrote:

COUNTIF has only one criterion (notwithstanding that Help calls the last
parameter "criteria" in the plural) so there is equivalent to your formula.

best wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Wazooli" wrote in message
...
Given the following array:

1024 20
100 30
600 20
700 10
800 10

Is there a COUNTIF equivalent to the following formula?
=SUMPRODUCT(--(A4:A8200),--(A4:A8/B4:B8=30))

I am just starting to appreciate the power of SUMPRODUCT, so I want to try
and understand limitations of it as well as replacement functions.

Also, check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wazooli" wrote in message
...
Thank you gentlemen. Much appreciated.

"Bernard Liengme" wrote:

COUNTIF has only one criterion (notwithstanding that Help calls the last
parameter "criteria" in the plural) so there is equivalent to your
formula.

best wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Wazooli" wrote in message
...
Given the following array:

1024 20
100 30
600 20
700 10
800 10

Is there a COUNTIF equivalent to the following formula?
=SUMPRODUCT(--(A4:A8200),--(A4:A8/B4:B8=30))

I am just starting to appreciate the power of SUMPRODUCT, so I want to
try
and understand limitations of it as well as replacement functions.

Hi
just in addition: maybe of interest for you:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"Wazooli" schrieb im Newsbeitrag
...
Given the following array:

1024 20
100 30
600 20
700 10
800 10

Is there a COUNTIF equivalent to the following formula?
=SUMPRODUCT(--(A4:A8200),--(A4:A8/B4:B8=30))

I am just starting to appreciate the power of SUMPRODUCT, so I want to try
and understand limitations of it as well as replacement functions.