COUNT COLLECT

Tony Oxa
Tony Oxa ✭✭✭✭
edited 08/07/23 in Formulas and Functions

Hi all,

I have a COUNT(COLLECT) formula that dosent seem to work. The returned value dosent match the actual number of Red Status projects under the portfolio named "X", also the column status is a symbol and not a word. In the formula i am using the refere to a spreadsheet.

Help please.

Tags:

Answers

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    Hi,

    Nobody can help you unless you provide enough detail.


    Adam.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Tony Oxa

    I agree that it would be helpful to have more information - could you post the formula you're using, and potentially a screen capture of the sheet you're looking to COUNT? (But please block out sensitive data).

    It sounds like you'll want to use a COUNTIFS:

    =COUNTIFS({Symbol Column}, "Red", {Portfolio Column}, "X")


    Even though the Symbol column has symbols, you'll want to search for that symbol using the word "Red". Here are some resources that may help you:

    Cross-sheet formulas / COUNTIFS Function

    Cheers,

    Genevieve

  • Tony Oxa
    Tony Oxa ✭✭✭✭

    This is the formula.

    =COUNT(COLLECT({All Portfolios Summary Roll-Up Reported Status}, {All Portfolios Summary Roll-Up Range Portfolio,[Primary Column]@row}, {All Portfolios Summary Roll-Up Reported Status,"Red"}))

    I need to count how many red status projects are under a portfolio by looking up to a spreadsheet that has all projects associated to a portfolio. So my table should say: for portfolio "X" there are 5 Red status projects. For portfolio "Y" there are 2 red status projects etc.

    I dont think a COUNTIF would work.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Tony Oxa

    You're right that a COUNTIF (singular) wouldn't work, but a COUNTIFS (plural) is exactly what you're looking for!

    Try:

    =COUNTIFS({All Portfolios Summary Roll-Up Range Portfolio}, [Primary Column]@row, {All Portfolios Summary Roll-Up Reported Status}, "Red")


    Notice that each of {these} needs to be closed off before your state your criteria:

    {Range}, "Criteria"

    or

    {Range}, [Criteria]@row

    Let me know if the formula above works for you.

    Cheers,

    Genevieve

  • Tony Oxa
    Tony Oxa ✭✭✭✭

    @Genevieve P. if i use countifs how its going to collect the number of projects, and i tried your suggestion it says #Unparsable.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Tony Oxa

    I assume you have the "Portfolios" listed in both the {Range Portfolio} and in the [Primary Column]@row, is that correct?

    I'm imagining your sheet set up so that your Primary Column has each Portfolio listed down in individual cells, and then the formula is in a column next to it.

    The COUNTIFS will then look in to your other sheet and filter down based on the "Portfolio" listed in the {Range Portfolio} and if the row is "Red" in your {Range Status}. Then it will output the number of rows that meet that criteria.

    Can you post a screen capture of how you put in the formula I suggested above? (But block out sensitive data)

    Thanks!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!