SOLVED

Summarize list based on value?

Copper Contributor

Please advise me on how to create a a summary in the red box area with only names that contains unblank notes. Any help would be much appreciated.

 

Thank you,

Monchi

 

summary ques.png

6 Replies
1. Take down that image as quickly as you can. You've posted an image that contains real names of real people.
2. create a dummy file with fictitious names (copy your real excel sheet and just change names) and then post that file on OneDrive or GoogleDrive and paste a link here that grants edit access to your file. That helps us help you far more effectively than an image.
best response confirmed by MPulse4 (Copper Contributor)
Solution

@MPulse4 

=INDEX(E$6:E$15,SMALL(IF($AM$6:$AM$15<>"",ROW($AM$6:$AM$15)-5),ROW($A1)))

You can try this formula, Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. In the example the formula is in cell B19 and filled across range B19:C22.

summary list.JPG

 

If you work with Excel 2019 or later you can apply FILTER.

=FILTER(E6:F15,AM6:AM15<>"")

filter.JPG

 

Thank you for the assist. I am using 365 - the filter formula works great on the row with the names and notes however the notes are not appearing in the section next to the filtered names. May please advise.

Thanks so much again for the help. (img is attached)summary filter ques.png

@OliverScheurich 

Also would it be possible to customize the filtered section with color and to create a border box that would resize along with the filter ?

@MPulse4 

It's not possible to customize the filtered section with color and a resizing border box. In the screenshot the FILTER function is in cell E74. You can drag the formula to cell K74 and the results are spilled in the "filter notes area".
 

Noted. Thank you again! @OliverScheurich 

1 best response

Accepted Solutions
best response confirmed by MPulse4 (Copper Contributor)
Solution

@MPulse4 

=INDEX(E$6:E$15,SMALL(IF($AM$6:$AM$15<>"",ROW($AM$6:$AM$15)-5),ROW($A1)))

You can try this formula, Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. In the example the formula is in cell B19 and filled across range B19:C22.

summary list.JPG

 

If you work with Excel 2019 or later you can apply FILTER.

=FILTER(E6:F15,AM6:AM15<>"")

filter.JPG

 

View solution in original post