Logical test false values skip to next row for next test

Essentially, I'm tasked with keeping a running list of new members joining an organization, and keeping an up-to-date RSVP list on a second sheet in the same workbook. I already have everything set up and formatted, but I'm having difficulty with sorting who's coming into another sheet, and who we're waiting on in a third sheet.

The three primary columns are "Business Name", "Orientation?", and "# of People" (Columns A, E, & F, respectively; B-D is contact info). The first column in my second sheet is supposed to comprise of a list of all Business Names in sheet 1 from Column A, who have "Yes" as their RSVP in Column E. Furthermore pulling the corresponding number of people expected to attend. E.g. Out of Companies A-E, only Companies B & C will attend and with 2 people each. My second sheet should then pull those two companies and begin a list.

I've been able to try using a variety of functions including: IF, IFERROR, INDEX, MATCH, ROW, ROWS, COLUMN, COLUMNS, COUNTIFS, and so on. So far, nothing has completely worked. The formula needs to account for new companies being added to the original list, and add them to the RSVP list if they are attending along with the # of people expected to attend. I already have a formula to get the total count of people expected, that's a simple SUM function. I consistently run into one of two problems:

1. The array formula for Column A in Sheet 2 detects the first "Yes" and displays its corresponding Business Name, then the next row shows the same name, and so does the entire array. And entire list of the name of the first business with a "Yes".
2. Or, the array formula accurately displays the names of every business with "Yes", though separated by blank rows for business not attending.

The second problem is the closest solution so far, but I need the formula to omit adding an empty row, and simply find the next "Yes". For example, if I use the formula =IF($E:$E="YES",$A:$A, ""), I'll get a blank row every time something other than "Yes" appears. Instead, I need a formula that says something along the line of "If Logical Test is False, skip to next row and test again. Repeat until Logical Test is True, then enter Value if True, and move to the next row & test again.

Sorry for the extremely long post everybody! I've attached a sample workbook based off my situation, below. Please feel free to ask any questions should there be any misunderstandings. Thanks for any help you guys can offer!


Sample Workbook.xlsx

Thank you Dibbins and Wilson! Both of these work great! You've saved me a lot of headache, and now I can focus on Thanksgiving. Have a good holiday!

Hi FDibbins,

I also faced a similar problem. I used the formula suggested by you and it is working properly. Though this was posted 5 years back, I still would want to thank you.

Thanks
Mrigansh Jain