Forum Discussion
Project Revenue by geographic location, filtering out some projects by name (SUMIFS?)
On the excluding range that I made with only 2 cells on Sheet 1, just put all the names that you want to exclude and adjust the reference properly to the entire range in the formulation.
In case you have a large dataset and do not want to write manually the excluding range you can write a formula as follows:
=FILTER(A2:C16,LEFT(A2:A16,4)="Game")
Where A2;C16 is the entire range of information and A2:A16 the range of the project names. This formula is only made to exclude any project starting with "Game" text. Adjust accordingly or add another condition if wanting multiple criteria.
For instance, to create the "excluding" range with any name starting with "Game" or "Fair" would be:
=FILTER(A2:C16,(LEFT(A2:A16,4)="Game")+(A2:A16="Fair"))
Created a filtered table excluding whatever you want and then using again the COUNTIF function. See attached document.
What if my projects have more specific names, and I want to filter all projects out that contain the words Festival & Game?
On the excluding range that I made with only 2 cells on Sheet 1, just put all the names that you want to exclude and adjust the reference properly to the entire range in the formulation.
In case you have a large dataset and do not want to write manually the excluding range you can write a formula as follows:
=FILTER(A2:C16,LEFT(A2:A16,4)="Game")
Where A2;C16 is the entire range of information and A2:A16 the range of the project names. This formula is only made to exclude any project starting with "Game" text. Adjust accordingly or add another condition if wanting multiple criteria.
For instance, to create the "excluding" range with any name starting with "Game" or "Fair" would be:
=FILTER(A2:C16,(LEFT(A2:A16,4)="Game")+(A2:A16="Fair"))