Formula Help Needed...

Natalie Nesbitt

Looking at the chart below I've identified the number of times a Agronomist name appears in a row from a master sheet. For example, Wayde Looker appears in 3 different row items, Ron Beyer 1, and Jason Sanders appears 14 times. How would I pull at a row level (by person), the status of each row to complete the chart? The numbers in columns 'Info Needed', 'In Progress' and 'Complete' need to total what's in column 'Total Rows'. I'm sure this is a simply task but when it comes to more complex formulas, I struggle. TIA!!

RebekahD

Hello @Natalie Nesbitt,

I think I understand what you are asking for so I'll give it a try, but let me know if I've misunderstood.

• You are trying to calculate how many of the total rows are in each status - "Info Needed", "In Progress" and "Complete.

The formula you are going to want to use is COUNTIFS which allows you to count the occurrences within certain parameters. It is structured as follows: COUNTIFS( range1, criterion1, [range2, ​criterion2, ​...] ). Range is the area you are searching, and Criterion is what it must be following.

The following would need to be setup

• {Agronomist name} - A reference to the Name in the Master Sheet (Just as you already have) - You can adjust the title of the reference and then the formulas will work
• {Status} - A reference to the status row in the master sheet.
• [Agronomist Status] - I'm working under the assumption that your provided column titles are the same as your sheet

In the "Info Needed" Column the formula would be

=COUNTIFS({Agronomist name},[Agronomist Status]@row,{Status},"Info Needed")

In the "In Progress" Column the formula would be

=COUNTIFS({Agronomist name},[Agronomist Status]@row,{Status},"In Progress")

In the "Complete" Column the formula would be

=COUNTIFS({Agronomist name},[Agronomist Status]@row,{Status},"Complete")

Let me know if these work for you!

Natalie Nesbitt

Thank you so muchfor the response RebekahD! Let me add a few more details on where the data resides that I need to pull from. My master file consist of an "Agronomist Name" column and a "Overall Status" column. The "Overall Status" column holds the status 'Complete', 'Info Needed' and 'In Progress' per line item Columns shown below. There are a number of hidden rows between the two.