Cell with 2 names in it causing error in formula

[Gambit79]

Hi
I have a master data spreadsheet that contains multiple columns including one with the name of a resource. generally most of the cells in this column contain only one name. however some of the cells contain 2 names for example "Joe Blogs / Jane Blogs". they all contain the same format.
on another sheet i have a formula that looks for the names in this column and then counts them based on multiple criteria. however that formula is skipping the cells that contain 2 names, which stuffs up the end result in terms of data accuracy.

is there a way in my formula to look for the name and still find it in the cells where there is 2 names? as I still want to count everytime "Joe Blogs" appears for example, and separately count all the Jane Blogs ones as well.

thanks

 

[Peter_SSs]

on another sheet i have a formula that looks for the names in this column and then counts them based on multiple criteria.

Can you post that formula?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Gambit79]

Hi Peter

the formula is:
=COUNTIFS(Table2[Solution CSM],[@Resource],Table2[Real Classification],'Formula ref sheet'!$B$4,Table2[Solution],'Formula ref sheet'!$C$11)+COUNTIFS(Table2[Solution CSM],[@Resource],Table2[Real Classification],'Formula ref sheet'!$B$4,Table2[Solution],'Formula ref sheet'!$C$8)

Some Notes:

• Table2 = the master data spreadsheet
• Solution CSM = the column that contains the names (sometimes has the two names)
• @resource = the name I am trying to find in the formula
• the other fields are more criteria to finish the count, those parts work without issue.

Version: Microsoft 365, Excel version 2008 (Build 13127.21668)

 

[Peter_SSs]

Version: Microsoft 365

As I suggested before, put this in your account details then it is always available to helpers. Otherwise you would need to re-write this every time you ask a question.

See if this formula does what you want.

=IFNA(ROWS(FILTER(Table2[Solution CSM],(ISNUMBER(SEARCH("/"&[@Resource]&"/","/"&Table2[Solution CSM]&"/")))*(Table2[Real Classification]='Formula ref sheet'!$B$4)*((Table2[Solution]='Formula ref sheet'!$C$11)+(Table2[Solution]='Formula ref sheet'!$C$8)),NA())),0)

 

[Gambit79]

thanks Peter
I have gone into my profile to see where I can add those details, can't see a section to edit though and add them. would you know where I do it?

also is the formula an Array formula? i put it into the sheet just as a copy and paste and it returned a result on my test subject of 0 when the result should be 5

 

[Michael M]

When you enter your profile ,select Account Details, then about halfway down the page !

 

[Peter_SSs]

I have gone into my profile to see where I can add those details, can't see a section to edit though and add them.

Just confirming what Michael said. Look here

also is the formula an Array formula? i put it into the sheet just as a copy and paste and it returned a result on my test subject of 0 when the result should be 5

With Excel 365 there should be no need to enter as an array formula as all formulas are treated as if they are. Perhaps we nee to see a simple set of sample data.

Here is mine.

Gambit79.xlsm
	A	B	C
4		a
5
6
7
8			c
9
10
11			b
Formula ref sheet

Gambit79.xlsm
	A	B	C	D	E	F	G	H
1
2		Solution CSM	Real Classification	Solution			Resource	Frmla2
3		Res1/Res2	a	b			Res1	2
4		Res2	a	b			Res2	5
5		Res3	a	dd			Res3	3
6		Res1/Res3	a	dd			Res4	0
7		Res2	a	b			Res5	0
8		Res3/Res2	a	b
9		Res1	xx	c
10		Res2	a	c
11		Res3	a	c
12		Res1	a	b
13		Res2	xx	b
14		Res3	a	b
15
Sheet1

Cell Formulas
Range	Formula
H3:H7	H3	=IFNA(ROWS(FILTER(Table2[Solution CSM],(ISNUMBER(SEARCH("/"&[@Resource]&"/","/"&Table2[Solution CSM]&"/")))*(Table2[Real Classification]='Formula ref sheet'!$B$4)*((Table2[Solution]='Formula ref sheet'!$C$11)+(Table2[Solution]='Formula ref sheet'!$C$8)),NA())),0)

In the right hand table, the count of 2 for Res1 comes from the left table rows 3 and 12
The count of 5 for Res2 comes from rows 3, 4, 7, 8 & 10
The count of 3 for Res3 comes from rows 8, 11 & 14

Have I misunderstood your requirement?