List Names Based on Criteria

[MWilliams]

I have a list of names used to track certain completed tasks. At the end of a quarter, people are supposed to have 3 tasks completed. I would like to get another list of the people who have not completed the required number of tasks.
As seen in the simple table below, I'd just like to see a list containing the names of John Doe and Sally Thompson, along with their totals.
Any help is greatly appreciated

FirstName	LastName	Jan	Feb	Mar	Total
John	Doe	1		1	2
Jane	Smith	1	1	1	3
Bob	Johnson	1	1	1	3
Sally	Thompson	1	1		2

<tbody>
</tbody>

 

[benhanson]

One of the easiest ways to view a quick summary is to filter.

step 1. click on a cell within the range (e.g. click the cell that contains the word "total")
step 2. click on the data tab in the task bar
step 3. click on the filter button
step 4. On the dropdown arrow in the cell which says "total", just keep 3 checked.
The data will be filtered for 3

Alternatively, you can use an IF Statement:
in the next column type this formula:

(assuming John Doe's total is in cell A6)
=if(A6=3,"All tasks complete","Tasks Incomplete")
then drag down the column for everybody.

You can type any text message you want displayed as long as its in quotation marks ("")

Hope this helped

 

[Aladin Akyurek]

I have a list of names used to track certain completed tasks. At the end of a quarter, people are supposed to have 3 tasks completed. I would like to get another list of the people who have not completed the required number of tasks.
As seen in the simple table below, I'd just like to see a list containing the names of John Doe and Sally Thompson, along with their totals.
Any help is greatly appreciated

FirstName	LastName	Jan	Feb	Mar	Total
John	Doe	1		1	2
Jane	Smith	1	1	1	3
Bob	Johnson	1	1	1	3
Sally	Thompson	1	1		2

<TBODY>
</TBODY>

FirstName	LastName	Jan	Feb	Mar	Total		Idx	FirstName	LastName	Total
John	Doe	1		1	2		1	John	Doe	2
Jane	Smith	1	1	1	3		4	Sally	Thompson	2
Bob	Johnson	1	1	1	3
Sally	Thompson	1	1		2

<COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4579" width=129><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5632" width=158><COL style="WIDTH: 48pt" span=6 width=64><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3953" width=111><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3726" width=105><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

H1: Idx (the header for an additional range in order to effect a fast set up)

I1:K1 houses the relevant headers for the results.

H2, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(1-($F$2:$F$5=3),ROW(F$2:$F$5)-ROW($F$2)+1),ROWS($H$2:H2)),"")

I2, just enter, copy across to K1, and down:

=IF($H2="","",INDEX($A$2:$F$5,$H2,MATCH(I$1,$A$1:$F$1,0)))

 

[markmzz]

Maybe this:

Layout

FirstName	LastName	Jan	Feb	Mar	Total		FirstName	LastName
John	Doe	1		1	2		John	Doe
Jane	Smith	1	1	1	3		Sally	Thompson
Bob	Johnson	1	1	1	3
Sally	Thompson	1	1		2
*********	*********	****	****	****	*****	**	*********	*********

<tbody>
</tbody>

Array formula in H2 and copy to the right and down - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(INDEX(A$2:A$5,SMALL(IF($F$2:$F$5<3,ROW($F$2:$F$5)-ROW($F$2)+1),ROWS(H$2:H2))),"")

Markmzz

 

[MWilliams]

Thank-you, folks. I'll work with these and get back.

This is actually for someone else and I'm going to show them a pivot table with a slicer, so they might like that option as well.

Thanks again!

 

[MWilliams]

Sorry, I have an update...

This workbook consists of multiple sheets. I was told they like the pivot table, and they would like one pivot table for each worksheet, but they also want one more pivot table on a summary sheet that is basically a summary of each of the other tables. I found this, but is there another way you might suggest accomplishing this?

Again, many thanks!

 

[markmzz]

Sorry, I have an update...

This workbook consists of multiple sheets. I was told they like the pivot table, and they would like one pivot table for each worksheet, but they also want one more pivot table on a summary sheet that is basically a summary of each of the other tables. I found this, but is there another way you might suggest accomplishing this?

Again, many thanks!

Hi MWilliams,

I think that the best way now is to create a new topic with what you have and what you want in details.

Maybe another user have another suggestion.

Markmzz

 

[Aladin Akyurek]

Sorry, I have an update...

This workbook consists of multiple sheets. I was told they like the pivot table, and they would like one pivot table for each worksheet, but they also want one more pivot table on a summary sheet that is basically a summary of each of the other tables. I found this, but is there another way you might suggest accomplishing this?

Again, many thanks!

For a multiple sheet consolidation, running a pivot table is the best option.