MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Countif, In Array


Posted by Colin on January 02, 2001 3:07 PM

I'm trying to count the number of cells that have a
value greater than 0 in the columns underneath a W.

So if the array looks like:

(W) (L) (L) (W)
(4) (0) (2) (0)

I'm trying to return 1.

If the array looks like:

(W) (L) (L) (W) (W) (L) (L) (W)
(4) (0) (2) (0) (8) (0) (9) (0)

I'm trying to return 2.

If it looks like:

(W) (L) (W) (L) (L) (W) (L) (W)
(4) (0) (0) (4) (0) (2) (0) (4)

I'm trying to return 3.

My best stab so far is:
{=COUNT(IF(Win="W",One))}
Where 'Win' is the Range (W) (L) (W) (L) (L) (W) (L) (W)
and 'One' is the Range (4) (0) (0) (4) (0) (2) (0) (4)

Unfortunately, this returns 4

TIA for any help!

Colin


Posted by cpod on January 02, 2001 3:16 PM

Try:

{=SUM(N(Win="w")*N(One>0))} Array Formula (8) (0) (9) (0)

Posted by Dave on January 02, 2001 3:30 PM


Hi Colin

You can only use an Array formula if your 2 named ranges have the same amount of Columns/Rows. If they do then you could use:

=SUM((Win="W")*(One>0))


Entered with Ctrl+Shift+Enter

  • OzGrid Business Applications

Posted by Colin on January 02, 2001 4:00 PM


Thanks to both of you.

Both solutions worked perfectly!

In case your interested, the actual formulae I used were:

{=SUM(IF(E$4:CA$4="W",E5:CA5))} - To Sum the Numbers
{=SUM((E$4:CA$4="W")*(E5:CA5>0))} - To Count the Numbers

To count and sum other rows I can just auto-fill.
I ended up not using named ranges for convenience.

Colin

Posted by Rick on October 20, 2001 3:48 PM

Re: Passwords

I'm trying to download free, full version, password cracker for Excel '97, can you help?