Sumproduct and wildcard
 

The data in my sheet is like:

Column A Column B
1 4602546wx
0 4615648dp
1 455586wd
1 465565ab

and it is a huge data

I want to count all the '1's where they have column B starting with 46.

Any help on how can I ahieve this by using wildcard would be greatly
appreciated.

KD.

Sumproduct and wildcard
 

=SUMPRODUCT(--(A1:A1000="1"),--(LEFT(B1:B1000,2)="46"))

If 1s are numbers (not TEXT) then:

=SUMPRODUCT(--(A1:A1000=1),--(LEFT(B1:B1000,2)="46"))


"kd" wrote:

The data in my sheet is like:

Column A Column B
1 4602546wx
0 4615648dp
1 455586wd
1 465565ab

and it is a huge data

I want to count all the '1's where they have column B starting with 46.

Any help on how can I ahieve this by using wildcard would be greatly
appreciated.

KD.

Sumproduct and wildcard
 

No need to test for 1 if it is just 1 and 0

=SUMPRODUCT(--(LEFT(B1:B1000,2)="46"),A1:A1000)

a tad more efficient

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Toppers" wrote in message
...
=SUMPRODUCT(--(A1:A1000="1"),--(LEFT(B1:B1000,2)="46"))

If 1s are numbers (not TEXT) then:

=SUMPRODUCT(--(A1:A1000=1),--(LEFT(B1:B1000,2)="46"))


"kd" wrote:

The data in my sheet is like:

Column A Column B
1 4602546wx
0 4615648dp
1 455586wd
1 465565ab

and it is a huge data

I want to count all the '1's where they have column B starting with 46.

Any help on how can I ahieve this by using wildcard would be greatly
appreciated.

KD.

Sumproduct and wildcard
 

=SUMIF(B:B,"46*",A:A)


"kd" wrote:

The data in my sheet is like:

Column A Column B
1 4602546wx
0 4615648dp
1 455586wd
1 465565ab

and it is a huge data

I want to count all the '1's where they have column B starting with 46.

Any help on how can I ahieve this by using wildcard would be greatly
appreciated.

KD.