Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Countif( 2 criteria = true ?)
Hi can you please help me...
I am trying count the number of items based on 2 criteria can you please help me..? Basically I have a sheet and in col A I have Code and col B I have name. eg: Col A - Col B 1001 - Mike 1001 - Dave 1001 - Keith 1002 - Terry 1001 - Mike What I am trynig to do is count how many say ("Mike" col B) with code of say ("1001" col A). so the answer according to the above will be "2". I know I can use the dsum function to do this but the problem is I am trying to get a formula in 1 cell to do the job as opposed to 2 rows + 3 columns.. hense is there a kind of countif(and) function or something I can use ??? Thanks. |
#2
|
|||
|
|||
Countif( 2 criteria = true ?)
=SUMPRODUCT(--(B2:B200="Mike"),--(A2:A200=1001))
-- HTH RP (remove nothere from the email address if mailing direct) "MyKool" wrote in message ... Hi can you please help me... I am trying count the number of items based on 2 criteria can you please help me..? Basically I have a sheet and in col A I have Code and col B I have name. eg: Col A - Col B 1001 - Mike 1001 - Dave 1001 - Keith 1002 - Terry 1001 - Mike What I am trynig to do is count how many say ("Mike" col B) with code of say ("1001" col A). so the answer according to the above will be "2". I know I can use the dsum function to do this but the problem is I am trying to get a formula in 1 cell to do the job as opposed to 2 rows + 3 columns.. hense is there a kind of countif(and) function or something I can use ??? Thanks. |
#3
|
|||
|
|||
Countif( 2 criteria = true ?)
Thanks thats great !!!
Please for my own knowledge, can you please tell me what is the double "-" is for ??? as in : = sumproduct(-- <-what is this for or what does it do ???? Many thanks "Bob Phillips" wrote: =SUMPRODUCT(--(B2:B200="Mike"),--(A2:A200=1001)) -- HTH RP (remove nothere from the email address if mailing direct) "MyKool" wrote in message ... Hi can you please help me... I am trying count the number of items based on 2 criteria can you please help me..? Basically I have a sheet and in col A I have Code and col B I have name. eg: Col A - Col B 1001 - Mike 1001 - Dave 1001 - Keith 1002 - Terry 1001 - Mike What I am trynig to do is count how many say ("Mike" col B) with code of say ("1001" col A). so the answer according to the above will be "2". I know I can use the dsum function to do this but the problem is I am trying to get a formula in 1 cell to do the job as opposed to 2 rows + 3 columns.. hense is there a kind of countif(and) function or something I can use ??? Thanks. |
#4
|
|||
|
|||
Countif( 2 criteria = true ?)
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "MyKool" wrote in message ... Thanks thats great !!! Please for my own knowledge, can you please tell me what is the double "-" is for ??? as in : = sumproduct(-- <-what is this for or what does it do ???? Many thanks "Bob Phillips" wrote: =SUMPRODUCT(--(B2:B200="Mike"),--(A2:A200=1001)) -- HTH RP (remove nothere from the email address if mailing direct) "MyKool" wrote in message ... Hi can you please help me... I am trying count the number of items based on 2 criteria can you please help me..? Basically I have a sheet and in col A I have Code and col B I have name. eg: Col A - Col B 1001 - Mike 1001 - Dave 1001 - Keith 1002 - Terry 1001 - Mike What I am trynig to do is count how many say ("Mike" col B) with code of say ("1001" col A). so the answer according to the above will be "2". I know I can use the dsum function to do this but the problem is I am trying to get a formula in 1 cell to do the job as opposed to 2 rows + 3 columns.. hense is there a kind of countif(and) function or something I can use ??? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif criteria only one word of few doesn't work | Excel Worksheet Functions | |||
Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
countif variable criteria | Excel Discussion (Misc queries) | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions |