Find a cell that contains certain criteria, and count the cells in a row that do not contain specific text.

[sabbuck]

I have an Excel sheet where I need to track completion progress. On Tab 1, I have a data that will constantly change (will be copy and pasting data from a downloaded report) but the number of columns and rows will stay the same. Each name in Column A is given a score for each module and there are 17 modules in total. On tab 2, I want to look up a name in tab 1 and return a count of the 17 modules that does not contain the text "N/Ans" and make it a %. On tab 1 I can get this information by using =(COUNTIF(B2:R2,"<>*N/Ans*"))/COUNTIF(B2:R2,"*"), as this sheet will be copied over whenever new data is available, I want to do some sort of VLookUp in tab 2 to return the same information. Is that possible? I've added screenshots below to help clarify.

 

[Sam_D_Ben]

May be something like this,

Test.xlsx
	A	B
1	Name	Progress
2	John Doe A	100%
3	John Doe B	94%
4	Jane Doe A	100%
5	Jane Doe B	71%
Tab 2

Cell Formulas
Range	Formula
B2:B5	B2	=SUMPRODUCT(('Tab 1'!A:A=A2)*('Tab 1'!B2:R2<>"N/Ans"))/COUNTIF('Tab 1'!B2:R2,"*")

 

[sabbuck]

Thank you @Sam_D_Ben but it's not returning the correct scores. The screeenshots I provided above are only an example, the real sheet contains about 90 rows and they won't always be in the same order as the tab I'm trying to retrieve the information from. I need something like a VLOOKUP but that will return a formula for a row adjacent to what is looked up.

 

[myall_blues]

Try this:

Book1
	A	B
1	George	100.0
2	Paul	94.1
3	Ringo	70.6
4	John	100.0
Tab2

Cell Formulas
Range	Formula
B1:B4	B1	=SUMPRODUCT(('Tab1'!$A$2:$A$5=A1)*--('Tab1'!$B$2:$R$5<>"N/Ans"))*100/17

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Name	Module 1	Module 2	Module 3	Module 4	Module 5	Module 6	Module 7	Module 8	Module 9	Module 10	Module 11	Module 12	Module 13	Module 14	Module 15	Module 16	Module 17
2	John	Result1	Result1	Result1	Result1	Result1	Result1	Result1	Result1	Result1	Result1	Result1	Result1	Result1	Result1	Result1	Result1	Result1
3	Paul	Result2	Result2	Result2	Result2	Result2	Result2	Result2	N/Ans	Result2	Result2	Result2	Result2	Result2	Result2	Result2	Result2	Result2
4	George	Result3	Result3	Result3	Result3	Result3	Result3	Result3	Result3	Result3	Result3	Result3	Result3	Result3	Result3	Result3	Result3	Result3
5	Ringo	Result4	Result4	Result4	Result4	Result4	Result4	Result4	N/Ans	N/Ans	Result4	Result4	N/Ans	N/Ans	Result4	N/Ans	Result4	Result4
Tab1

Couple of other things:
- It helps if you post data using XL2BB as it means we don't need to reproduce your data
- It's helpful if you tell us what version(s) of Excel you are using as that affects the answer. You can update your profile to show your Excel version.

 

[kvsrinivasamurthy]

Try. In B2 of Tab 2 and copied down

=COUNTIF(INDEX('Tab 1'!$B$2:$S$8,MATCH('Tab 1'!$A2,'Tab 1'!$A$2:$A$8,0),0),"N/A")/COUNTA(INDEX('Tab 1'!$B$2:$S$8,MATCH('Tab 1'!$A2,'Tab 1'!$A$2:$A$8,0),0))

 

[sabbuck]

Thanks for the heads up @myall_blues, I've updated my profile to say I'm using Microsoft 365 and will use XL2BB in future.

Your solution worked perfectly, thank you so much!

 

[myall_blues]

Thanks for the heads up @myall_blues, I've updated my profile to say I'm using Microsoft 365 and will use XL2BB in future.

Your solution worked perfectly, thank you so much!

You’re welcome. Thanks for the feedback.