Compex COUNTIF

D

dave

Is it possible to create a complex COUNTIF that will count the number
of rows in a range where, for each row, column A = 100 and column B is
greater than 5?

I realise that I could create a formula cell on each line that contains
the result of the evaluation for each line and base the COUNTIF on
that. However, I don't own the spreadsheet and that operation would not
be acceptable.

Thanks in advance.

Dave
 
P

Pete_UK

You could try this array function* in a cell somewhere:

=SUM(IF((A1:A1000=100)*(B1:B1000)>5,1,0))

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you will need to use CTRL-SHIFT-ENTER instead of
just ENTER. If you do this correctly, then Excel will wrap curly braces
{ } around the formula - you should not type these yourself.

Hope this helps - a SUMPRODUCT formula would also do this.

Pete
 
B

Bob Phillips

=SUMPRODUCT(--(A1:A1000=100),--(B1:B1000>5))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

dave

Thanks for the help guys - I ended up using SUMPRODUCT.

BTW, why does each component require a prefix of "--" ?
 
D

dave

Thanks for the help guys - I ended up using SUMPRODUCT.

BTW, why does each component require a prefix of "--" ?
 
B

Bob Phillips

-- stuff?

We are supposed to surround it with a technical air of mystery, to make us
seem smart!

Bob
 
D

Dave Peterson

It's the stuff that dreams are made of!

or...

Stuff that in your pipe and smoke it.

or...

Stuff the magic dragon, wait, that's not right.
 
R

RagDyeR

Don't let these guys fool you!

You *don't* need those unaries ( -- ).

This works just as well:

=SUMPRODUCT((A1:A1000=100)*(B1:B1000>5))

And with 4 *less* keystrokes.

Like they said, they like to make 'stuff' seem complicated.<bg>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Thanks for the help guys - I ended up using SUMPRODUCT.

BTW, why does each component require a prefix of "--" ?
 
J

Jay

The xldynamic page on SUMPRODUCT is excellent, although MAC users be
advised that the site will crash the Safari browser. I use Firefox when
referring to it.

Jason
 
B

Bob Phillips

Any idea why Jason?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top