Search code examples
google-sheetsfilterswitch-statement

Filter Multiple Criteria Google Sheets

I have created a Dashboard using the Google Sheets GeoTag chart. I have managed to the get the filter to work for "All Countries" but I cannot seem to get it to produce a full list when "All Activities" is selected.

Could someone please advise what I am maybe doing wrong?

=iferror(if($C$3="All Countries",filter({'Plan Details'!C5:C,'Plan Details'!D5:D,'Plan Details'!E5:E,'Plan Details'!F5:F,'Plan Details'!T5:T},not('Plan Details'!C5:C="")*('Plan Details'!B5:B100=$C$2))
,filter({'Plan Details'!C5:C100,'Plan Details'!D5:D100,'Plan Details'!E5:E100,'Plan Details'!F5:F100,'Plan Details'!T5:T100},('Plan Details'!C5:C100=$C$3)*('Plan Details'!B5:B100=$C$2))),"No activities planned within this country")

https://docs.google.com/spreadsheets/d/1CeNVHl5f0fD9Pk3MTYldZBUxFNuKp6UBOLa2LIBfJ4M/edit?usp=sharing

The above is a link to the sheet. Any help would be greatly appreciated!

Also, if anyone knows why England/Wales/Scotland/UK does not seem to work for the Geotag please let me know

Thanks, Shea

I have got the formula working for the most part, it is just the second condition I cannot get working. I tried * and + but cannot work it out

Solution

  • You may try:

    Here's one approach you may test out:

    =ifna(filter({'Plan Details'!C5:F,'Plan Details'!T5:T},
         if(left(C2,4)="All ",'Plan Details'!B5:B<>"",'Plan Details'!B5:B=C2),
         if(left(C3,4)="All ",'Plan Details'!C5:C<>"",'Plan Details'!C5:C=C3)),
 "NO_ACTS_")

    enter image description here