How to count a coloum if two conditions are met

[Mr Fujisawa]

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",STATS!I:I)))}
as an array function, but it isn't working

What else can I do?

Thanks

 

[Mike H]

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="Online"))
--
Mike

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

 

[Per Jessen]

Hi

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

Regards,
Per

 

[Roger Govier]

Hi

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

 

[Bernard Liengme]

=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

 

[Mr Fujisawa]

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