Forum Discussion

burnskrl's avatar
burnskrl
Copper Contributor
Feb 05, 2025

COUNTIFS to return values in a table

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!! 

  • Thank you everyone for your help!! I was completely overcomplicating and really just needed a simple IF= statement. But this was a great learning experience!!

  • burnskrl's avatar
    burnskrl
    Copper Contributor

    Thank you everyone for your help!! I was completely overcomplicating and really just needed a simple IF= statement. But this was a great learning experience!!

  •  COUNTA(FilteredBudgets) returns 1 if FILTER returns #CALC! error. It counts all not empty cells, doesn't matter they have values or errors. Thus IFERROR never returns zero.

    Better ROWS instead of COUNTA.

    • burnskrl's avatar
      burnskrl
      Copper Contributor

      Thank you!

      I ended up using COUNT instead of COUNTA. It resolved my problem...I will try ROWS as well to test in case of future need. I do want to be sure that COUNT is correct not just here, but in other instances where I am counting data to output. Do you have thoughts on whether that would work across other scenarios similar to this one?

      • COUNT counts numbers only. Thus ignores texts, Booleans and errors. If you'd like to ignore errors only that could be

        =SUM(--NOT( ISERROR( array ) ) )

        What to use depends on what exactly you'd like to count. If vertical or horizontal size of the array, when ROWS and COLUMNS work. If number of elements in 2D array ignoring errors when as above. Etc.

        As everything in Excel practically any calculation could be done by several ways. For example, in addition to COUNT or ROWS for the sample, we may use

        IF( ISERROR( @FilteredBudgets ), 0, COUNTA( FilteredBudgets ) )

        , perhaps something else. All depends on situation.

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    Can you please attach a sample file showing your sample input data and then show your desired output manually. We will try to make that output using formula. Also please mention your Excel edition/version info so that we can suggest appropriate formula.

  • How about this for Projects <100:

     

    =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)
        ),
        ProjectsCount, IFERROR(COUNTA(FilteredBudgets), 0),
        IF(ProjectsCount = 0, "None", ProjectsCount)
    )
    

     

    • burnskrl's avatar
      burnskrl
      Copper Contributor

      This looks the same as the first test I posted...

       

Resources