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?
