conditional occurrences of specific string in a range

BeingYogi

Board Regular
Joined
Jul 24, 2011
Messages
166
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 01London
Test 02LA
Test 03London
Test 04LA
Fest 01London
Test 03LA
Test 04London
Fest 04LA
#VALUE!

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

snb_

Well-known Member
Joined
Nov 9, 2009
Messages
567

ADVERTISEMENT

or

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

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,370
Messages
5,595,770
Members
414,017
Latest member
surajks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top