COUNTIFS

AOW0415

Hi, I'm trying to write a COUNTIFS formula and keep getting errors of "INVALID REF", INCORRECTARG or "UNPARSEABLE." I'm new to SmartSheet and can't figure out my errors.

Here's what I'm trying to do:

Create a bar chart to track status of individual products as a whole. I have 8 different projects at various stages. I'd like to visually see in a stacked bar chart which program is where in the stages. The chart is pulling Stages on the horizonal axis and the numerical value on the vertical axis.

How do I write a formula that is:

1. Pulling information from a main "feeder" page for product
2. Implementing the Product Name into the formula sheet based on Status?
3. Then place that text data into numeral value to count.
4. I also need this charted data to be changing with status changes by specific product.

Attached is the way I need the chart to look.

For the chart above, I did the line copy function and then found out copied lines don't change in status and it won't work but my project leader still wants to see a chart like this. I tried funning off a report, as well, but the bar chart will not break down colors for a quick visual...only reports off one axis of data.

If anyone knows how I can more easily do this, I'm open to other ideas. The only criteria is 1. it has to look broken down by color like above and will have to "live" as status' are changed.

Genevieve P.

Hi @AOW0415

No problem at all! I'd be happy to walk you through this formula step-by-step. Your source sheet and summary sheet look great, we can definitely use this, and no - your columns don't have to be next to each other. 🙂

This is the end-result that we want to get for that [Product 1] column:

=COUNTIFS({Product Column Source Sheet}, "Product 1", {Stage Column Source Sheet}, [Primary Column]@row)

However you're right, there are 2 ranges, {these}, that are looking at columns in a different sheet. Each of them will be its own, separate range, which is why they don't need to be next to each other in that source sheet.

Start with just the COUNTIFS. Once you have the open parentheses, if your cursor is hovering right after that, you should see a little helper box that has a hyperlink saying "reference another sheet":

=COUNTIFS(

Click on this, and it will open up a window to all of the sheets you have access to. Search for your source sheet and select it from the list in the left.

Then, the first range we want is the "Product" column. Click on the column name in this pop-up window and it will select the entire column:

Before you click "insert reference", something I personally always do is re-name what the text will appear in my formula, to make it easier to look at later. You can do this by editing the "Sheet reference name" at the top:

Then when you insert the reference it will appear with that name in the formula: {Product Column}

Put a comma and type our first criteria, the product name in quotes, then another comma:

The tooltip box tells us what to do next: list another {range}, comma, then another criteria. In your case, your range is another cross-sheet reference, so click that blue link again. It will automatically open up to the correct sheet.

Click the column name of your "Test Status" column this time, and don't forget to re-name it! (If you want to)

Insert this, add a comma, and then select the cell in your primary column to the left:

Note that mine says "Stage@row" but your column is the Primary Column so it should be [Primary Column]@row.

That should be it! 😊

One final thing to add here is make sure that you don't "Edit" a reference in the future.

Doing this will update what column {these words} are looking at for every single instance in your sheet. Instead, if you want to replace a reference, delete it out from the formula and add a new reference.

If you still aren't getting the correct result, it would be helpful to see the formula you tried, copy/pasted or open in the sheet.

Cheers!

Genevieve.

Genevieve P.

Hi @AOW0415

Here's the structure of how I'd set up my metric sheet in this scenario... the Primary Column would be all the stages and then I would have each column for the Product:

In this instance I'd use a COUNTIFS Function to COUNT how many rows per Product were in each Stage:

=COUNTIFS({Product Column}, "Product 1", {Stage Column}, Stage@row)

See: Create cross sheet references to work with data in another sheet

Note that you would need to change out the "Product 1" to be "Product 2" etc. The formula will automatically update as new rows are added in your other sheet or as current rows are updated to different stages.

Then you can select this and create a bar chart.

If this hasn't helped, it would be useful to see a screen capture of your source sheet where the data is stored, but please block out sensitive information.

Thanks!

Genevieve

AOW0415

Genevieve,

Thank you SO much for helping and taking the time to put this together. What you're instructing makes complete sense, however, I'm struggling a little more with how to write the formula after linking to another sheet. Here's the requested screenshot of where the data is stored. I have done the tutorial on how to link another page, which I have done successfully, but my formula isn't correct now. :-( The columns for Product and Test Status are not next to each other on the main sheet. Do I have to move them also?

AOW0415

Here's my summary sheet which is fed by the main sheet

Genevieve P.

Hi @AOW0415

No problem at all! I'd be happy to walk you through this formula step-by-step. Your source sheet and summary sheet look great, we can definitely use this, and no - your columns don't have to be next to each other. 🙂

This is the end-result that we want to get for that [Product 1] column:

=COUNTIFS({Product Column Source Sheet}, "Product 1", {Stage Column Source Sheet}, [Primary Column]@row)

However you're right, there are 2 ranges, {these}, that are looking at columns in a different sheet. Each of them will be its own, separate range, which is why they don't need to be next to each other in that source sheet.

Start with just the COUNTIFS. Once you have the open parentheses, if your cursor is hovering right after that, you should see a little helper box that has a hyperlink saying "reference another sheet":

=COUNTIFS(

Click on this, and it will open up a window to all of the sheets you have access to. Search for your source sheet and select it from the list in the left.

Then, the first range we want is the "Product" column. Click on the column name in this pop-up window and it will select the entire column:

Before you click "insert reference", something I personally always do is re-name what the text will appear in my formula, to make it easier to look at later. You can do this by editing the "Sheet reference name" at the top:

Then when you insert the reference it will appear with that name in the formula: {Product Column}

Put a comma and type our first criteria, the product name in quotes, then another comma:

The tooltip box tells us what to do next: list another {range}, comma, then another criteria. In your case, your range is another cross-sheet reference, so click that blue link again. It will automatically open up to the correct sheet.

Click the column name of your "Test Status" column this time, and don't forget to re-name it! (If you want to)

Insert this, add a comma, and then select the cell in your primary column to the left:

Note that mine says "Stage@row" but your column is the Primary Column so it should be [Primary Column]@row.

That should be it! 😊

One final thing to add here is make sure that you don't "Edit" a reference in the future.

Doing this will update what column {these words} are looking at for every single instance in your sheet. Instead, if you want to replace a reference, delete it out from the formula and add a new reference.

If you still aren't getting the correct result, it would be helpful to see the formula you tried, copy/pasted or open in the sheet.

Cheers!

Genevieve.

AOW0415

This worked!!!! Thank you does not even begin to express my appreciation but THANK YOU

Genevieve P.

I'm so glad to hear it! You're welcome 🙂