Crossing Multiple Colum's on a Different Sheet

A.Valadez

Good afternoon,

I am stuck trying to gather data to build a dashboard with the appropriate information. I am trying to get a formula that compares which department is selected from a drop-down column (Dept Requesting). I then need this information to be compared with a different drop-down column that displays status (Status). I have found a formula to gather that refences the Main Sheet and calculates the average of the (Dept Requesting) column but don't understand how to also add the Status from the same sheet. Any help would be appreciated! I have spent far too long trying to research this to no avail.

This was the formula that I am using to count the number of items each department is requesting.

=COUNTIFS({Task Tracker Range 2}, "Facilities")

Lauren Dominique

Hi! If I am correctly understanding, you have one sheet that has a listing of requests by department with varying request statuses, and you are creating a second sheet where you want to count the number of requests submitted per department. Yes?

If so, this formula would return the number of requests submitted by the faculties department, regardless of which status they are in: =COUNTIF({Requesting Department}, “Facilities”)

If you wanted to take it a step further and be able to count how many of the facilities department’s requests are (for example) In Progress, that formula would look like this: =COUNTIFS(Requesting Department}, “Facilities”, {Request Status}, “In Progress”)

Hope this is helpful!

A.Valadez

Good morning Lauren!

Thank you for reaching out. Unfortunately, that did not work. I need to provide more information. I would like to provide the total count of all status's from the (Status) column. And have it sorted by each (Dept. Requesting Status) column. These numbers are to be pulled from a different sheet titled {2. Project Tracker}

For example: Operations requested 10 projects and 5 are on hold, 2 are completed, and 3 are in progress.

The formula I listed out was able to pull the total amount of each Dept. Requesting from the column but I am unaware of how to calculate the number of different status's.

Thank you so much for your help!

Lauren Dominique

Thanks for this extra info! So, if I am understanding correctly, you’re looking for something similar to the below, where the numbers in each cell updates automatically as progress is documented in the main sheet for each department?

If yes, your column formula for the “Complete” column would be: =COUNTIFS({Requesting Department}, [Department]@row, {Request Status}, “Complete”)

In the above formula, {Requesting Department} should be a cross-sheet reference to the column in the 2. Project Tracker sheet where the departments are listed. {Request Status} should be a cross-sheet reference into the same 2. Project Tracker sheet in the column where the status is documented

By doing so, the formula I wrote above COUNT any of the fields in the Project Tracker Sheet that have the same department as the Department@row in the Metrics sheet AND a status in the Project Tracker Sheet that matches the text in the quotations (so in the case above, “Complete”).

Is this helpful?

A.Valadez

Good morning Lauren,

I appreciate the help but unfortunately it is still not working. I am not sure if its because it is not linking to the other sheet. Shouldn't the formula have a section with {2. Project Tracker}? I have also tried to use the formula on the same sheet and its still not populating.

Genevieve P.

Hey @A.Valadez

Can you post screen captures showing your set-up, but please block out sensitive data?

A.Valadez

Good morning Genevieve,

Thank you for reaching out please see attached photos. The titles are the name of the sheets.

4. Dept Support Page.png

2. Project Tracker.png

Genevieve P.

Hi @A.Valadez

It looks like the first formula is working! 🙂 You're successfully finding how many rows have the word "Facilities".

I will note that since you have that typed out in the column to the left you don't need to write it in the formula.

=COUNTIFS({Task Tracker Range 2}, Description@row)

This way you can drag the same formula into all the cells and have it dynamically reference the content in the Description column.

In your other column you can do the same formula but add the extra "Complete" criteria and divide it by your total:

=COUNTIFS({Task Tracker Range 2}, Description@row, {Task Tracker Status Column}, "Complete") / COUNTIFS({Task Tracker Range 2}, Description@row)

Cheers,

Genevieve

A.Valadez

Good morning Genevieve,

Un fortunately the formula you provided is still not working =[. I am not sure what is going wrong.

Genevieve P.

Hey @A.Valadez

What you do mean when you say it's not working, are you getting an error or an incorrect result? Can we see screen captures again?

A.Valadez

I am getting an invalid error.

Genevieve P.

Hey @A.Valadez

Did you copy the text {Task Tracker Status Column}? You'll need to create this as your own unique reference in that sheet, finding the correct page and the correct column.

Here's more information: Create cross sheet references to work with data in another sheet

A.Valadez

I changed it to the correct sheet reference and column but still receive the invalid reference error. Would you be able to show me how its written with the titles of the reference sheet below?

2. Project Tracker

Genevieve P.

Hi @A.Valadez

Cross sheet references can have any text in {these}, which is why it won't work to copy/paste from the Community - you'll need to create them in the sheet and title the reference whatever you'd like 🙂

Here's how to create a cross-sheet reference:

Make sure the hyperlink says "Reference Another Sheet" and not "Edit Reference". Then in the pop-up window select the correct sheet and column:

In this pop-up window, you can title the reference to this sheet and column to be anything! This is the text that appears in the formula. Then when you get to the next reference, repeat the same process:

To spell out which columns you should be looking at, I'll change the text {in these} to show you what column to select:

=COUNTIFS({Department Requesting Column}, Description@row, {Status Column}, "Complete") / COUNTIFS({Department Requesting Column}, Description@row)

Notice that in the two separate COUNTIFS you're referencing the same column - the Department Requesting column.

The math works out to this:

=COUNT of Complete for this Department / COUNT of total rows for this Department

Does this help?

Genevieve

A.Valadez

Good afternoon Genevieve,

Apologies for the late response, our company had major shift in projects. Unfortunately the information provided was still not beneficial. I have followed the texts perfectly and still receiving an error code. =[

Smartsheet2.png

smartsheet 1.png

Genevieve P.

Hi @A.Valadez

A few small details!

• It looks like you're missing a quote 🙂

See where it says "Complete? Make sure that the entire word is in quotes: "Complete"

• Make sure your {References} are going to the right place

The COUNTIFS should look the exact same, but the first one should have one extra pair of {reference} and "criteria".

In your image, I see that you start with {2. Project Tracker Range 1}, but in your second COUNTIFS you have {Dept. Requesting Column}. These should be the same reference so they would have the same name.

=COUNTIFS({Range 1}, Description@row, {Range 2}, "Complete") / COUNTIFS({Range 1}, Description@row)

Notice that in both it's the same range and criteria.

Cheers,

Genevieve