conditional occurrences of specific string in a range

[BeingYogi]

Hi Forum,


Want count the conditional occurrences of specific string in a range. Using the following formula bout giving error.
If someone can find where I am mistaking.

=SUMPRODUCT(--((SEARCH("Test",$A$1:$A$8,1)>0)),--($B$1:$B$8="London"))

Test 01	London
Test 02	LA
Test 03	London
Test 04	LA
Fest 01	London
Test 03	LA
Test 04	London
Fest 04	LA
#VALUE!

<tbody>
</tbody>

 

[VoG]

Try

=SUMPRODUCT(--(LEFT(A1:A8,4)="Test"),--(B1:B8="London"))

 

[VoG]

Cross posted http://www.excelforum.com/excel-gen...ccurrences-of-specific-string-in-a-range.html

 

[BeingYogi]

Awesome buddy !

 

[snb_]

or

=SUMPRODUCT(N(LEFT(A1:A8;4)&(B1:B8)="TestLonden"))

 

[taurean]

If you have Excel 2007+ version then you can use:

=COUNTIFS(A1:A8,"Test*",B1:B8,"London")

which tends to be faster than SUMPRODUCT.

 

[T. Valko]

Hi Forum,


Want count the conditional occurrences of specific string in a range. Using the following formula bout giving error.
If someone can find where I am mistaking.

=SUMPRODUCT(--((SEARCH("Test",$A$1:$A$8,1)>0)),--($B$1:$B$8="London"))

Test 01	London
Test 02	LA
Test 03	London
Test 04	LA
Fest 01	London
Test 03	LA
Test 04	London
Fest 04	LA
#VALUE!

<TBODY>
</TBODY>

If you're using Excel 2007 or later...

=COUNTIFS(A1:A8,"Test*",B1:B8,"London")

Better to use cells to hold the criteria.

D1 = Test
E1 = London

=COUNTIFS(A1:A8,D1&"*",B1:B8,E1)