Which (logical) function?
 

Table was codes...if the ID begins with a w and the number in the second
column is a 1 add the ones (which are male ;)

A B
1 ID Gender
2 W01 2
3 W02 1
4 G01 1
5 W08 1

I have: =COUNTIF(A$2:$A$5,"=w??")*AND(COUNTIF(B$2:$B$5,"1" ))...which isn't
correct because it returns 3 (all the w's) I suppose.

What do I do???

TIA!

Which (logical) function?
 

For more than one criteria, use Sumproduct, as in:
=sumproduct((left(a2:a5)="w")*(b2:b5=1))

Regards,
Fred

"JustJill" wrote in message
...
Table was codes...if the ID begins with a w and the number in the second
column is a 1 add the ones (which are male ;)

A B
1 ID Gender
2 W01 2
3 W02 1
4 G01 1
5 W08 1

I have: =COUNTIF(A$2:$A$5,"=w??")*AND(COUNTIF(B$2:$B$5,"1" ))...which
isn't
correct because it returns 3 (all the w's) I suppose.

What do I do???

TIA!

Which (logical) function?
 

Sorry, make that:
=sumproduct((left(a2:a5,1)="w")*(b2:b5=1))

Fred

"Fred Smith" wrote in message
...
For more than one criteria, use Sumproduct, as in:
=sumproduct((left(a2:a5)="w")*(b2:b5=1))

Regards,
Fred

"JustJill" wrote in message
...
Table was codes...if the ID begins with a w and the number in the second
column is a 1 add the ones (which are male ;)

A B
1 ID Gender
2 W01 2
3 W02 1
4 G01 1
5 W08 1

I have: =COUNTIF(A$2:$A$5,"=w??")*AND(COUNTIF(B$2:$B$5,"1" ))...which
isn't
correct because it returns 3 (all the w's) I suppose.

What do I do???

TIA!

Which (logical) function?
 

Another way

=SUMPRODUCT(--(LEFT(A1:A5)&(B1:B5)="W1"))

--
Jacob (MVP - Excel)


"JustJill" wrote:

Table was codes...if the ID begins with a w and the number in the second
column is a 1 add the ones (which are male ;)

A B
1 ID Gender
2 W01 2
3 W02 1
4 G01 1
5 W08 1

I have: =COUNTIF(A$2:$A$5,"=w??")*AND(COUNTIF(B$2:$B$5,"1" ))...which isn't
correct because it returns 3 (all the w's) I suppose.

What do I do???

TIA!