Curious about SUMPRODUCT

[Guest]

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:A8>200),--(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.

 

[Dave R.]

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.

 

[Bernard Liengme]

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

 

[Guest]

Thank you gentlemen. Much appreciated.

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

 

[Bob Phillips]

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

--

HTH

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

 

[Frank Kabel]

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