How to count a coloum if two conditions are met

  • Thread starter Thread starter Mr Fujisawa
  • Start date Start date
M

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
 
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.
 
=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
 
Back
Top