Count Occurrences of text with 2 conditions and wild cards

jon gordyn

New Member
Joined
Jan 24, 2005
Messages
5
Count Occurrences of text with 2 conditions and wild cards

the only stupid question is the one not asked. here is what i can not figure out how to do:

Sheet A:

- a list of names in column A, appearing only once
- columns B through whatever contain a specific category (some 33 different ones)

Sheet B

- contains multiple entries for each person
- contains the same categories as sheet A, but only in 4 cells. basically each cell can have a multiple category entries.


I need to write a formula that takes the persons name from sheet A and scans through a range on sheet B. when it finds that persons entry, It counts the total number of occurrences for a given string in the same row taking into account wild cards. then place that number on Sheet A. the work book pretty much deals exclusively with words.

I have been using SUM(IF statements but i can not get it to read wild cards

any help would be appreciated

Jon
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Re: Count Occurrences of text with 2 conditions and wild car

Welcome to the board!

You could try this:


Enter

=SUMPRODUCT(($F$4:$F$7=$A4)*(LEN($G$4:$I$7)-LEN(SUBSTITUTE($G$4:$I$7,B$3,""))))/LEN(B$3)

in B4 and drag right / down.
Book1
ABCDEFGHI
1SheetASheetB
2
3Namesxxxyyyzzz
4John441Johnxxxyyyyyyyyyzzz
5Steve105Johnyyyxxxxxxxxx
6Bill000Stevezzzzzzxxxzzz
7Jonas000Stevezzzzzz
8
9
Sheet3
 

jon gordyn

New Member
Joined
Jan 24, 2005
Messages
5
Re: Count Occurrences of text with 2 conditions and wild car

so far I have not been able to get that string to work, I have reformatted the data so that appears more as the demo does. the only issue is the names in B have the email address added to the name. So the names in A have to have the email address added inorder to match up with names in B. any Ides?


thanks
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Re: Count Occurrences of text with 2 conditions and wild car

Do you mean that the names in A column should be a part of a string that is in F column?

Maybe you could post some sample data to clarify?
 

Forum statistics

Threads
1,148,393
Messages
5,746,441
Members
424,019
Latest member
dpteo

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