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

sabbuck

New Member
Joined
Aug 21, 2017
Messages
27
Office Version
  1. 365
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.
 

Attachments

  • Tab 1.png
    Tab 1.png
    18.3 KB · Views: 10
  • Tab 2.png
    Tab 2.png
    5.2 KB · Views: 10
May be something like this,
Test.xlsx
AB
1NameProgress
2John Doe A100%
3John Doe B94%
4Jane Doe A100%
5Jane Doe B71%
Tab 2
Cell Formulas
RangeFormula
B2:B5B2=SUMPRODUCT(('Tab 1'!A:A=A2)*('Tab 1'!B2:R2<>"N/Ans"))/COUNTIF('Tab 1'!B2:R2,"*")
 
Upvote 0
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.
 
Upvote 0
Try this:

Book1
AB
1George100.0
2Paul94.1
3Ringo70.6
4John100.0
Tab2
Cell Formulas
RangeFormula
B1:B4B1=SUMPRODUCT(('Tab1'!$A$2:$A$5=A1)*--('Tab1'!$B$2:$R$5<>"N/Ans"))*100/17


Book1
ABCDEFGHIJKLMNOPQR
1NameModule 1Module 2Module 3Module 4Module 5Module 6Module 7Module 8Module 9Module 10Module 11Module 12Module 13Module 14Module 15Module 16Module 17
2JohnResult1Result1Result1Result1Result1Result1Result1Result1Result1Result1Result1Result1Result1Result1Result1Result1Result1
3PaulResult2Result2Result2Result2Result2Result2Result2N/AnsResult2Result2Result2Result2Result2Result2Result2Result2Result2
4GeorgeResult3Result3Result3Result3Result3Result3Result3Result3Result3Result3Result3Result3Result3Result3Result3Result3Result3
5RingoResult4Result4Result4Result4Result4Result4Result4N/AnsN/AnsResult4Result4N/AnsN/AnsResult4N/AnsResult4Result4
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.
 
Upvote 0
Solution
Try. In B2 of Tab 2 and copied down
Excel Formula:
=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))
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

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
Back
Top