Extract with or without criteria

[BenElliott]

Hello, all.

Several years ago I watched a ExcelMagic trick that extracted a series of data based on a criteria but I can't seem to find it now.

Here is what I need:
My data table, currently 2,800 lines long is in an Excel table labled "SpeakerTalks". Column 'A' is labled "Speaker Full Name", Column 'B' is labled "Location" and Column 'C' is labled "Status" with the table starting call A1. Status is shown by either "Good" or "Moderate" indicates the type of speaker. Sometimes I need to select either "Good" or "Moderate" and sometimes I need to ignore the choice and have all selected.

Here is my formula to count the necessary cells (which at the moment only selectes all) is contained in cell F1 with the location criteria selection in F2 and Status in F3:
{=SUM(IF(FREQUENCY(IF(SpeakersTalks[Speaker Full Name]<>"",IF(SpeakersTalks[Location]=F2,MATCH(SpeakersTalks[Speaker Full Name],SpeakersTalks[Speaker Full Name],0))),ROW(SpeakersTalks[Speaker Full Name])-ROW($C$2)+1),1))}

My problem, for which I cannot now find the video, is how to select either or all of the data in Status. Any pointers to how to select the location and include either or all of the status criteria would be very welcome.

Many thanks.

Ben

 

[ravishankar]

You can use DATa filters with appropriate selection of criteria.
Ravi shankar

 

[Aladin Akyurek]

{=SUM(IF(FREQUENCY(IF(SpeakersTalks[Speaker Full Name]<>"",IF(SpeakersTalks[Location]=F2,IF(SpeakersTalks[Status]=IF(F3="*",SpeakersTalks[Status],F3),MATCH(SpeakersTalks[Speaker Full Name],SpeakersTalks[Speaker Full Name],0)))),ROW(SpeakersTalks[Speaker Full Name])-ROW(INDEX(SpeakersTalks[Speaker Full Name],1,1))+1),1))}

where IF(F3="*",SpeakersTalks[Status],F3) means every status when * is entered in F3.

 

[BenElliott]

Thank you so much for your help. The IF=IF("*") opens up a totally new option for me.

I'm very grateful for you giving your attention to my problem

Regards,

Ben

 

[Aladin Akyurek]

Ben,

You are welcome.

Aladin

 

[BenElliott]

One question, though, if I may? How does this construction enable me to select either or all? I have put your amendment into my formula and changed the data validation range to allow for the third choice of "select all" and it does indeed select either "Good" or "Moderate" from status but I'm not sure how I can then get both "Good" and "Moderate" combined.

Thanks,

Ben

 

[Aladin Akyurek]

One question, though, if I may? How does this construction enable me to select either or all? I have put your amendment into my formula and changed the data validation range to allow for the third choice of "select all" and it does indeed select either "Good" or "Moderate" from status but I'm not sure how I can then get both "Good" and "Moderate" combined.

Thanks,

Ben

You can have * as an option in your data validation list. Or All if so desired. When * is selected in F3, the formula will admit every status value. If Moderate is selected in F3, the formula will only consider

SpeakersTalks[Status] = "Moderate"


If you have All as an option in your validation list, change F3 = "*" to F3 = "All".

 

[BenElliott]

That's great. Thanks.

Ben