SOLVED

Project Revenue by geographic location, filtering out some projects by name (SUMIFS?)

Copper Contributor

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:

KellyPPlus_1-1715781496716.png

 

Tab 2:

KellyPPlus_2-1715781543368.png

 

4 Replies

@KellyPPlus 

 

Created a filtered table excluding whatever you want and then using again the COUNTIF function. See attached document.

@Martin_Angosto 

 

What if my projects have more specific names, and I want to filter all projects out that contain the words Festival & Game?

 

KellyPPlus_0-1715788581224.png

 

 

best response confirmed by KellyPPlus (Copper Contributor)
Solution

@KellyPPlus 

 

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"))

@Martin_Angosto @KellyPPlus 

 

See attached example to automate the generation of the excluding names.

1 best response

Accepted Solutions
best response confirmed by KellyPPlus (Copper Contributor)
Solution

@KellyPPlus 

 

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"))

View solution in original post