|
Countif, two criterias need help
Hi
i need to know, how much people belongs to the number in Colum A - if in colum C is written "ISM". A B C 1 1 Meier ISM 2 3 Huber ISM 3 2 Schmitz UPA 4 2 Mayer ISM 5 1 Mueller UPA 6 1 Hase ISM if somebody can help me, i would appreciate! |
Countif, two criterias need help
If your number is in A8, enter in B8:
=SUMPRODUCT((A8=A$1:A$6)*("ISM"=C$1:C$6)) HTH -- AP "Axel" a écrit dans le message de ... Hi i need to know, how much people belongs to the number in Colum A - if in colum C is written "ISM". A B C 1 1 Meier ISM 2 3 Huber ISM 3 2 Schmitz UPA 4 2 Mayer ISM 5 1 Mueller UPA 6 1 Hase ISM if somebody can help me, i would appreciate! |
Countif, two criterias need help
A B C FORMULA IN RESULT IN D COLUMN D COLUMN 1 1 Meier ISM =IF(RIGHT(c2,3)="ism",A2,"")....1 2 3 Huber ISM =IF(RIGHT(c3,3)="ism",A3,"").... 2 3 2 Schmitz UPA =IF(RIGHT(c4,3)="ism",A4,"")..... 4 2 Mayer ISM =IF(RIGHT(c5,3)="ism",A5,"").... 4 5 1 Mueller UPA =IF(RIGHT(c6,3)="ism",A6,"")..... 6 1 Hase ISM =IF(RIGHT(c7,3)="ism",A7,"").....6 HOPE THIS HELPS REGARDS VENRAM -- venram ------------------------------------------------------------------------ venram's Profile: http://www.excelforum.com/member.php...o&userid=33813 View this thread: http://www.excelforum.com/showthread...hreadid=535884 |
Countif, two criterias need help
As I read it, it only needs
=COUNTIF(A1:A8,"ISM") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... If your number is in A8, enter in B8: =SUMPRODUCT((A8=A$1:A$6)*("ISM"=C$1:C$6)) HTH -- AP "Axel" a écrit dans le message de ... Hi i need to know, how much people belongs to the number in Colum A - if in colum C is written "ISM". A B C 1 1 Meier ISM 2 3 Huber ISM 3 2 Schmitz UPA 4 2 Mayer ISM 5 1 Mueller UPA 6 1 Hase ISM if somebody can help me, i would appreciate! |
Countif, two criterias need help
Thanks AP
but its not really what i want. in the first step i tried countif("$A$1:$C$1;1) and i got 3 people but thats not correct, because i only have to count the peolpe which have in colum C written "ISM" and in colum A an 1 f.e. THX Axel "Ardus Petus" wrote: If your number is in A8, enter in B8: =SUMPRODUCT((A8=A$1:A$6)*("ISM"=C$1:C$6)) HTH -- AP "Axel" a écrit dans le message de ... Hi i need to know, how much people belongs to the number in Colum A - if in colum C is written "ISM". A B C 1 1 Meier ISM 2 3 Huber ISM 3 2 Schmitz UPA 4 2 Mayer ISM 5 1 Mueller UPA 6 1 Hase ISM if somebody can help me, i would appreciate! |
Countif, two criterias need help
Thanks venram
but its not really what i need. in the first step i tried countif("$A$1:$C$1;1) and i got 3 people but thats not correct, because i only have to count the peolpe which have in colum C written "ISM" and in colum A an 1 f.e. THX Axel "venram" wrote: A B C FORMULA IN RESULT IN D COLUMN D COLUMN 1 1 Meier ISM =IF(RIGHT(c2,3)="ism",A2,"")....1 2 3 Huber ISM =IF(RIGHT(c3,3)="ism",A3,"").... 2 3 2 Schmitz UPA =IF(RIGHT(c4,3)="ism",A4,"")..... 4 2 Mayer ISM =IF(RIGHT(c5,3)="ism",A5,"").... 4 5 1 Mueller UPA =IF(RIGHT(c6,3)="ism",A6,"")..... 6 1 Hase ISM =IF(RIGHT(c7,3)="ism",A7,"").....6 HOPE THIS HELPS REGARDS VENRAM -- venram ------------------------------------------------------------------------ venram's Profile: http://www.excelforum.com/member.php...o&userid=33813 View this thread: http://www.excelforum.com/showthread...hreadid=535884 |
Countif, two criterias need help
Thanks Bob,
but the first column ist only the rownumbering of my examplespreedsheet. this system underpresses the leading spaces. A B C 1 Meier ISM 3 Huber ISM 2 Schmitz UPA 2 Mayer ISM 1 Mueller UPA 1 Hase ISM maybe now its more clearly. i have two criterias column A and column C THX Axel "Bob Phillips" wrote: As I read it, it only needs =COUNTIF(A1:A8,"ISM") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... If your number is in A8, enter in B8: =SUMPRODUCT((A8=A$1:A$6)*("ISM"=C$1:C$6)) HTH -- AP "Axel" a écrit dans le message de ... Hi i need to know, how much people belongs to the number in Colum A - if in colum C is written "ISM". A B C 1 1 Meier ISM 2 3 Huber ISM 3 2 Schmitz UPA 4 2 Mayer ISM 5 1 Mueller UPA 6 1 Hase ISM if somebody can help me, i would appreciate! |
Countif, two criterias need help
Try my response.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Axel" wrote in message ... Thanks venram but its not really what i need. in the first step i tried countif("$A$1:$C$1;1) and i got 3 people but thats not correct, because i only have to count the peolpe which have in colum C written "ISM" and in colum A an 1 f.e. THX Axel "venram" wrote: A B C FORMULA IN RESULT IN D COLUMN D COLUMN 1 1 Meier ISM =IF(RIGHT(c2,3)="ism",A2,"")....1 2 3 Huber ISM =IF(RIGHT(c3,3)="ism",A3,"").... 2 3 2 Schmitz UPA =IF(RIGHT(c4,3)="ism",A4,"")..... 4 2 Mayer ISM =IF(RIGHT(c5,3)="ism",A5,"").... 4 5 1 Mueller UPA =IF(RIGHT(c6,3)="ism",A6,"")..... 6 1 Hase ISM =IF(RIGHT(c7,3)="ism",A7,"").....6 HOPE THIS HELPS REGARDS VENRAM -- venram ------------------------------------------------------------------------ venram's Profile: http://www.excelforum.com/member.php...o&userid=33813 View this thread: http://www.excelforum.com/showthread...hreadid=535884 |
Countif, two criterias need help
Hi Bob
if i do, what you responsed then i the result is 4, but in reality its 2; because of the two criterias (in column A=1 and in column C=ISM) THX Axel "Bob Phillips" wrote: Try my response. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Axel" wrote in message ... Thanks venram but its not really what i need. in the first step i tried countif("$A$1:$C$1;1) and i got 3 people but thats not correct, because i only have to count the peolpe which have in colum C written "ISM" and in colum A an 1 f.e. THX Axel "venram" wrote: A B C FORMULA IN RESULT IN D COLUMN D COLUMN 1 1 Meier ISM =IF(RIGHT(c2,3)="ism",A2,"")....1 2 3 Huber ISM =IF(RIGHT(c3,3)="ism",A3,"").... 2 3 2 Schmitz UPA =IF(RIGHT(c4,3)="ism",A4,"")..... 4 2 Mayer ISM =IF(RIGHT(c5,3)="ism",A5,"").... 4 5 1 Mueller UPA =IF(RIGHT(c6,3)="ism",A6,"")..... 6 1 Hase ISM =IF(RIGHT(c7,3)="ism",A7,"").....6 HOPE THIS HELPS REGARDS VENRAM -- venram ------------------------------------------------------------------------ venram's Profile: http://www.excelforum.com/member.php...o&userid=33813 View this thread: http://www.excelforum.com/showthread...hreadid=535884 |
Countif, two criterias need help
Sorry it should have been
=COUNTIF(C1:C8,"ISM") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Axel" wrote in message ... Thanks Bob, but the first column ist only the rownumbering of my examplespreedsheet. this system underpresses the leading spaces. A B C 1 Meier ISM 3 Huber ISM 2 Schmitz UPA 2 Mayer ISM 1 Mueller UPA 1 Hase ISM maybe now its more clearly. i have two criterias column A and column C THX Axel "Bob Phillips" wrote: As I read it, it only needs =COUNTIF(A1:A8,"ISM") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... If your number is in A8, enter in B8: =SUMPRODUCT((A8=A$1:A$6)*("ISM"=C$1:C$6)) HTH -- AP "Axel" a écrit dans le message de ... Hi i need to know, how much people belongs to the number in Colum A - if in colum C is written "ISM". A B C 1 1 Meier ISM 2 3 Huber ISM 3 2 Schmitz UPA 4 2 Mayer ISM 5 1 Mueller UPA 6 1 Hase ISM if somebody can help me, i would appreciate! |
Countif, two criterias need help
I thought that you said that the numbers were just row numbers, so where
does 1 come from? If you have 1,2, etc. in column A then use =SUMPRODUCT(--(A$1:A$6=1),--(C$1:C$6="ISM")) as Ardus suggested. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Axel" wrote in message ... Hi Bob if i do, what you responsed then i the result is 4, but in reality its 2; because of the two criterias (in column A=1 and in column C=ISM) THX Axel "Bob Phillips" wrote: Try my response. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Axel" wrote in message ... Thanks venram but its not really what i need. in the first step i tried countif("$A$1:$C$1;1) and i got 3 people but thats not correct, because i only have to count the peolpe which have in colum C written "ISM" and in colum A an 1 f.e. THX Axel "venram" wrote: A B C FORMULA IN RESULT IN D COLUMN D COLUMN 1 1 Meier ISM =IF(RIGHT(c2,3)="ism",A2,"")....1 2 3 Huber ISM =IF(RIGHT(c3,3)="ism",A3,"").... 2 3 2 Schmitz UPA =IF(RIGHT(c4,3)="ism",A4,"")..... 4 2 Mayer ISM =IF(RIGHT(c5,3)="ism",A5,"").... 4 5 1 Mueller UPA =IF(RIGHT(c6,3)="ism",A6,"")..... 6 1 Hase ISM =IF(RIGHT(c7,3)="ism",A7,"").....6 HOPE THIS HELPS REGARDS VENRAM -- venram ------------------------------------------------------------------------ venram's Profile: http://www.excelforum.com/member.php...o&userid=33813 View this thread: http://www.excelforum.com/showthread...hreadid=535884 |
Countif, two criterias need help
sorry Bob but this version also gives a result of 4.
"Bob Phillips" wrote: Sorry it should have been =COUNTIF(C1:C8,"ISM") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Axel" wrote in message ... Thanks Bob, but the first column ist only the rownumbering of my examplespreedsheet. this system underpresses the leading spaces. A B C 1 Meier ISM 3 Huber ISM 2 Schmitz UPA 2 Mayer ISM 1 Mueller UPA 1 Hase ISM maybe now its more clearly. i have two criterias column A and column C THX Axel "Bob Phillips" wrote: As I read it, it only needs =COUNTIF(A1:A8,"ISM") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... If your number is in A8, enter in B8: =SUMPRODUCT((A8=A$1:A$6)*("ISM"=C$1:C$6)) HTH -- AP "Axel" a écrit dans le message de ... Hi i need to know, how much people belongs to the number in Colum A - if in colum C is written "ISM". A B C 1 1 Meier ISM 2 3 Huber ISM 3 2 Schmitz UPA 4 2 Mayer ISM 5 1 Mueller UPA 6 1 Hase ISM if somebody can help me, i would appreciate! |
Countif, two criterias need help
Are you wanting to get ?
Number with A=1 and C=ISM answer 2 Number with A=2 and C=ISM answer 1 Number with A=3 and C=ISM answer 1 Thats how I read your request, then the following works Row A B 8 1 {=SUM(IF($A$1:$A$6=$A8,IF($C$1:$C$6="ISM",1,0),0)) } 9 2 {=SUM(IF($A$1:$A$6=$A9,IF($C$1:$C$6="ISM",1,0),0)) } 10 3 {=SUM(IF($A$1:$A$6=$A10,IF($C$1:$C$6="ISM",1,0),0) )} A8:A10 are a list of numbers to *look for* I tend to use {=SUM...} for this sort of thing, then counting of the 1, can be changed to a column in the original data, so that it could sum soemthing else if required. Steve On Tue, 25 Apr 2006 04:14:02 -0700, Axel wrote: Hi Bob if i do, what you responsed then i the result is 4, but in reality its 2; because of the two criterias (in column A=1 and in column C=ISM) THX Axel "Bob Phillips" wrote: Try my response. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Axel" wrote in message ... Thanks venram but its not really what i need. in the first step i tried countif("$A$1:$C$1;1) and i got 3 people but thats not correct, because i only have to count the peolpe which have in colum C written "ISM" and in colum A an 1 f.e. THX Axel "venram" wrote: A B C FORMULA IN RESULT IN D COLUMN D COLUMN 1 1 Meier ISM =IF(RIGHT(c2,3)="ism",A2,"")....1 2 3 Huber ISM =IF(RIGHT(c3,3)="ism",A3,"").... 2 3 2 Schmitz UPA =IF(RIGHT(c4,3)="ism",A4,"")..... 4 2 Mayer ISM =IF(RIGHT(c5,3)="ism",A5,"").... 4 5 1 Mueller UPA =IF(RIGHT(c6,3)="ism",A6,"")..... 6 1 Hase ISM =IF(RIGHT(c7,3)="ism",A7,"").....6 HOPE THIS HELPS REGARDS VENRAM -- venram ------------------------------------------------------------------------ venram's Profile: http://www.excelforum.com/member.php...o&userid=33813 View this thread: http://www.excelforum.com/showthread...hreadid=535884 -- Steve |
| All times are GMT +1. The time now is 06:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com