Compex COUNTIF

[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

 

[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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[dave]

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

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

 

[dave]

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

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

 

[Dave Peterson]

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Bob Phillips]

-- stuff?

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

Bob

 

[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.

 

[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 "--" ?

 

[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

 

[Bob Phillips]

Any idea why Jason?

--
HTH

Bob Phillips

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