Forum Discussion
Solved
Project Revenue by geographic location, filtering out some projects by name (SUMIFS?)
I'm working on a spreadsheet and would love help on a formula. (SUMIFS?) The first tab tracks all my projects completed by row, and columns include project name, revenue, and location. The next tab I'd like rows by geographic location, and columns to include location, total revenue, and revenue from all project types except 2. I'm using an excel similar to the attached, and would like to see revenue by state, without including revenue from Fairs or Festivals. Any help is appreciated!
Tab 1:
Tab 2:
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"))
