Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count with conditions? | Excel Discussion (Misc queries) | |||
Regonise coloum A while adding up coloum B to give a result in C | Excel Discussion (Misc queries) | |||
how do i count the number of cells used in a coloum? | Excel Discussion (Misc queries) | |||
Count with 2 conditions, second one OR | Excel Worksheet Functions | |||
Count ifs - 2 conditions | Excel Discussion (Misc queries) |