Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

best way to count something in one column using criteria in another column

2 views
Skip to first unread message

Hankjam

unread,
May 23, 2012, 2:45:24 PM5/23/12
to
Hi
I would like to know the best way to count the number of cells that
have Pass in one column but only covering those in week 20 in another
column

A B

19 Pass
19 Pass
19 Fail
19 Pass
20 Pass
20 Fail
20 Pass
21 Fail
21 Pass

Thank you very much

Hj

Claus Busch

unread,
May 23, 2012, 2:58:13 PM5/23/12
to
Hi,

Am Wed, 23 May 2012 19:45:24 +0100 schrieb Hankjam:

> I would like to know the best way to count the number of cells that
> have Pass in one column but only covering those in week 20 in another
> column

> 19 Pass
> 19 Pass
> 19 Fail
> 19 Pass
> 20 Pass
> 20 Fail
> 20 Pass
> 21 Fail
> 21 Pass

in versions < 2007:
=SUMPRODUCT(--(A1:A100=20),--(B1:B100="Pass"))
xl2007 or xl2010:
=COUNTIFS(A:A,20,B:B,"Pass")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hankjam

unread,
May 23, 2012, 3:38:32 PM5/23/12
to
On Wed, 23 May 2012 20:58:13 +0200, Claus Busch
<claus...@t-online.de> wrote:

>=SUMPRODUCT(--(A1:A100=20),--(B1:B100="Pass"))
Hi Claus

Works a treat, many thanks
Hj
0 new messages