COUNT COLLECT

Tony Oxa

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.

BullandKhmer

Hi,

Nobody can help you unless you provide enough detail.

Adam.

Genevieve P.

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

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.

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

@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.

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

Tony Oxa