Count Occurrences of text with 2 conditions and wild cards

[jon gordyn]

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

 

[fairwinds]

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
	A	B	C	D	E	F	G	H	I
1	SheetA					SheetB
2
3	Names	xxx	yyy	zzz
4	John	4	4	1		John	xxxyyy	yyy	yyyzzz
5	Steve	1	0	5		John	yyy	xxx	xxxxxx
6	Bill	0	0	0		Steve	zzzzzz	xxxzzz
7	Jonas	0	0	0		Steve	zzzzzz
8
9
Sheet3

 

[jon gordyn]

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]

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?