How to count a coloum if two conditions are met
 

I want to count the number of entries in a column if its value = X but only
if another column = Y, kind of like merging two countif statemnts
so one countif is
=COUNTIF(STATS!E:E,B3) where coloum B is a list of persons' initials
and the second is
=countif(STATS!I:I,"ONLINE")

So I want to count the number of rows with "online" in colomn I, but only on
row's where coloum E = the value in B3

I have tried the following formula
{=COUNT(IF(STATS!E:E=B3,IF(STATS!I:I="ONLINE",STAT S!I:I)))}
as an array function, but it isn't working

What else can I do?

Thanks

How to count a coloum if two conditions are met
 

Hi,

Try this

=SUMPRODUCT((E1:E20=B3)*(I1:I20="Online"))

ir if the formula is on another sheet

=SUMPRODUCT((Stats!E1:E20=B3)*(Stats!I1:I20="Onlin e"))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mr Fujisawa" wrote:

I want to count the number of entries in a column if its value = X but only
if another column = Y, kind of like merging two countif statemnts
so one countif is
=COUNTIF(STATS!E:E,B3) where coloum B is a list of persons' initials
and the second is
=countif(STATS!I:I,"ONLINE")

So I want to count the number of rows with "online" in colomn I, but only on
row's where coloum E = the value in B3

I have tried the following formula
{=COUNT(IF(STATS!E:E=B3,IF(STATS!I:I="ONLINE",STAT S!I:I)))}
as an array function, but it isn't working

What else can I do?

Thanks

How to count a coloum if two conditions are met
 

Hi

=Sumproduct(--(STATS!E:E=B3),--(STATS!I:I="ONLINE"))

Regards,
Per


"Mr Fujisawa" <Mr skrev i meddelelsen
...
I want to count the number of entries in a column if its value = X but
only
if another column = Y, kind of like merging two countif statemnts
so one countif is
=COUNTIF(STATS!E:E,B3) where coloum B is a list of persons' initials
and the second is
=countif(STATS!I:I,"ONLINE")

So I want to count the number of rows with "online" in colomn I, but only
on
row's where coloum E = the value in B3

I have tried the following formula
{=COUNT(IF(STATS!E:E=B3,IF(STATS!I:I="ONLINE",STAT S!I:I)))}
as an array function, but it isn't working

What else can I do?

Thanks

How to count a coloum if two conditions are met
 

Hi

Try
=SUMPRODUCT(--($E$1:$E$100=B3),--($I$1:$I$1000="ONLINE"),$I$1:$I$1000)
--
Regards
Roger Govier

Mr Fujisawa wrote:
I want to count the number of entries in a column if its value = X but only
if another column = Y, kind of like merging two countif statemnts
so one countif is
=COUNTIF(STATS!E:E,B3) where coloum B is a list of persons' initials
and the second is
=countif(STATS!I:I,"ONLINE")

So I want to count the number of rows with "online" in colomn I, but only on
row's where coloum E = the value in B3

I have tried the following formula
{=COUNT(IF(STATS!E:E=B3,IF(STATS!I:I="ONLINE",STAT S!I:I)))}
as an array function, but it isn't working

What else can I do?

Thanks

How to count a coloum if two conditions are met
 

=SUMPRODUCT(--(STATS!E$1:E$100=B3),--(STATS!I$1:I$100="ONLINE"))
You cannot use full column references with SUMPRODUCT (except with Excel
2007) so you need to adjust the ranges in this formula.

In Excel 2007
EITHER:
=SUMPRODUCT(--(STATS!E:E=B3),--(STATS!I:I="ONLINE"))
OR you can use COUNTIFS (note the final S)
=COUNTIFS(STATS!E:E,B3,STATS!I1:I,"ONLINE")
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Mr Fujisawa" <Mr wrote in message
...
I want to count the number of entries in a column if its value = X but
only
if another column = Y, kind of like merging two countif statemnts
so one countif is
=COUNTIF(STATS!E:E,B3) where coloum B is a list of persons' initials
and the second is
=countif(STATS!I:I,"ONLINE")

So I want to count the number of rows with "online" in colomn I, but only
on
row's where coloum E = the value in B3

I have tried the following formula
{=COUNT(IF(STATS!E:E=B3,IF(STATS!I:I="ONLINE",STAT S!I:I)))}
as an array function, but it isn't working

What else can I do?

Thanks

How to count a coloum if two conditions are met
 

Thank's for the replies everyone, each one worked.

"Mr Fujisawa" wrote:

I want to count the number of entries in a column if its value = X but only
if another column = Y, kind of like merging two countif statemnts
so one countif is
=COUNTIF(STATS!E:E,B3) where coloum B is a list of persons' initials
and the second is
=countif(STATS!I:I,"ONLINE")

So I want to count the number of rows with "online" in colomn I, but only on
row's where coloum E = the value in B3

I have tried the following formula
{=COUNT(IF(STATS!E:E=B3,IF(STATS!I:I="ONLINE",STAT S!I:I)))}
as an array function, but it isn't working

What else can I do?

Thanks