COUNTIFS to return values in a table

[Windows Server]

Hello!

Working with a large dataset and seeking to break up the data into different columns based on amount. I am looking to separate projects <100K, projects >=100K, projects <=1M, and projects >1M. When I use the COUNTA function, I get a #Spill! error. When I use COUNTIF or COUNTIFS (to specify the 100K<=x<=1M), my output for managers that don't have projects that meet the criteria is '1', rather than the specified 'None'. 

 

Here are a few of the solutions that I have tried:

=LET(
    TPMList, FILTER($A:$A, ($A:$A<>"") * ($A:$A<>"Project Manager") * ($E:$E<>"N/A")),
    CurrentPM, A2,
    FilteredBudgets, FILTER(Table_Projects[Current Budget GR],
        (Table_Projects[Project Manager]=CurrentPM) *
        (Table_Projects[Period]=MAX(Table_Projects[Period])) *
        (Table_Projects[Current Budget GR]>100000) *
        (Table_Projects[Current Budget GR]<1000000)
    ),
    ProjectsCount, IFERROR(COUNTA(FilteredBudgets), 0),
    IF(ProjectsCount = 0, "None", ProjectsCount)
)

 

This one gives me a formula error:

=LET(
    TrPMList, FILTER($A:$A, ($A:$A<>"") * ($A:$A<>"Project Manager") * ($E:$E<>"N/A")),
    CurrentPM, A2,
    FilteredBudgets, FILTER(Table_Projects[Current Budget GR],
        (Table_Projects[Project Manager]=CurrentPM) *
        (Table_Projects[Period]=MAX(Table_Projects[Period])) *
    ),
    ProjectsCount, COUNTIFS((FilteredBudgets, ">100000") * (FilteredBudgets, "<1000000")), 0),
    IF(ProjectsCount = 0, "None", ProjectsCount)
)

 

This one returns the correct project number between 100K and 1M, but does not return "None" for projects that don't meet the criteria.

=LET(
    TrPMList, FILTER($A:$A, ($A:$A<>"") * ($A:$A<>"Project Manager") * ($E:$E<>"N/A")),
    CurrentPM, A3,
    FilteredBudgets, FILTER(Table_Projects[Current Budget GR],
        (Table_Projects[Project Manager]=CurrentPM) * (Table_Projects[Period]=MAX(Table_Projects[Period])) *
        (Table_Projects[Current Budget GR]>100000) * (Table_Projects[Current Budget GR]<1000000)
    ),
    ProjectsCount, COUNTA(FilteredBudgets),
    IF(ProjectsCount > 0, ProjectsCount, "None")
)

 

Any help is greatly appreciated!! 

View the full article