Prev: Sorting a portion of a worksheet
Next: HOW CAN i DEVELOP A FORMULA THAT RESTRICY SYMBOLS CHARACTERS TO B
From: JustJill on 28 Apr 2010 19:03
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!
From: Fred Smith on 28 Apr 2010 20:19
For more than one criteria, use Sumproduct, as in:
=sumproduct((left(a2:a5)="w")*(b2:b5=1))

Regards,
Fred

"JustJill" <JustJill(a)discussions.microsoft.com> wrote in message
news:62BFBEA4-1416-4793-B8DA-67B53EA8F6EE(a)microsoft.com...
> 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!

From: Fred Smith on 28 Apr 2010 22:51
Sorry, make that:
=sumproduct((left(a2:a5,1)="w")*(b2:b5=1))

Fred

"Fred Smith" <fsmith11(a)yahooo.com> wrote in message
news:Ods9eGz5KHA.348(a)TK2MSFTNGP02.phx.gbl...
> For more than one criteria, use Sumproduct, as in:
> =sumproduct((left(a2:a5)="w")*(b2:b5=1))
>
> Regards,
> Fred
>
> "JustJill" <JustJill(a)discussions.microsoft.com> wrote in message
> news:62BFBEA4-1416-4793-B8DA-67B53EA8F6EE(a)microsoft.com...
>> 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!
>

From: Jacob Skaria on 29 Apr 2010 01:05
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!
 | 
Pages: 1
Prev: Sorting a portion of a worksheet
Next: HOW CAN i DEVELOP A FORMULA THAT RESTRICY SYMBOLS CHARACTERS TO B