Countif, two criterias need help

A

Axel

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!
 
A

Ardus Petus

If your number is in A8, enter in B8:
=SUMPRODUCT((A8=A$1:A$6)*("ISM"=C$1:C$6))

HTH
 
V

venram

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
VENRA
 
B

Bob Phillips

As I read it, it only needs

=COUNTIF(A1:A8,"ISM")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

Axel

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
 
A

Axel

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
 
A

Axel

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
 
B

Bob Phillips

Try my response.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

Axel

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
 
B

Bob Phillips

Sorry it should have been

=COUNTIF(C1:C8,"ISM")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

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)
 
S

SteveW

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
 
Top