Extracting Row Numbers from Specific Values

%3CLINGO-SUB%20id%3D%22lingo-sub-1616686%22%20slang%3D%22en-US%22%3EExtracting%20Row%20Numbers%20from%20Specific%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1616686%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20creating%20a%20training%20tracker%20for%20my%20company.%20The%20current%20program%20uses%201's%20to%20delineate%20certified%20employees.%20These%20are%20the%20headers%20to%20my%20Table%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%201546px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215080i28F87FDC72D99277%2Fimage-dimensions%2F1546x48%3Fv%3D1.0%22%20width%3D%221546%22%20height%3D%2248%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20columns%20in%20orange%20indicate%20a%20task%20to%20be%20certified%20in.%3C%2FP%3E%3CP%3EMy%20company%20has%20asked%20me%20to%20track%20the%20%25trained%20for%20each%20shift.%20(1st%2C2nd%2C3rd)%20Is%20there%20a%20way%20for%20me%20to%20pull%20the%20rows%20of%20employees%20based%20on%20their%20shift%3F%20Then%20I%20can%20use%20the%20function%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCountA((Extractedrows(E%3AV))%2F(Countblank(Extractedrows(E%3AV))%20%2BCountA((Extractedrows(E%3AV)))%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20calculate%20percentage%20of%20employees%20trained%20per%20shift%3C%2FP%3E%3CP%3E'Extractedrows'%20%3D%20Numbers%20of%20rows%20that%20ar%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1616686%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hi, I am creating a training tracker for my company. The current program uses 1's to delineate certified employees. These are the headers to my Table

Capture.PNG

The columns in orange indicate a task to be certified in.

My company has asked me to track the %trained for each shift. (1st,2nd,3rd) Is there a way for me to pull the rows of employees based on their shift? Then I can use the function 

=CountA((Extractedrows(E:V))/(Countblank(Extractedrows(E:V)) +CountA((Extractedrows(E:V))) 

to calculate percentage of employees trained per shift

'Extractedrows' = Numbers of rows that include either '1st', '2nd', or '3rd' shifts

1 Reply
Highlighted

@Tingchi 

As variant that could be

=SUMPRODUCT((shiftColumnRange="1st")*(tasksRange<>""))

where for example first range is C2:C100 and second one E2:E100. Similar to calculate blank cells, just change <> on = .