Complex Formulas requiring a lot of editing

Sean Taber

Hello,

I need to count the number of projects in a certain status, work type by person.  I created this formula and it is working but it requires a lot of editing.  =COUNTIFS({Assigned To - BD}, "Charles Kimball", {Implementation Status - BD}, "Pending Kick Off", {Work Type - BD}, "Advanced Billing").  This formula is looking at another sheet to get the counts from.

There are 45 possible Assigned Tos, 4 Implementation Statuses, and 14 Work Types.  I went through this once already for one person.  So I have the base formulas down for the imp statuses and work types.  I only need to change the Assigned to but that is still a lot of editing.  Before I officially go down this road, I wanted to check and see if there is a better solution.  Is @cell or @row an option?  

I added a screen shot to help show what I'm trying to do.  This is just for one person.  I need to do this for 43 more people.

Thank you,

Sean

Andrée Starå

Hi Sean,

You can, for example, point to a cell with the value for the assigned to instead of the text in the formula.

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done

Paul Newcome

To expand on Andree's comment (which is the right idea)...

 

Try using:

=COUNTIFS({Assigned To - BD}, [Name of column Name is In]@row, {Implementation Status - BD}, "Pending Kick Off", {Work Type - BD}, "Advanced Billing")

If your "Assigned to" in your summary sheet is in a list, reference the cell directly instead of the name. You could then drag fill, and it will automatically reference the correct name instead of having to edit each formula individually.

If the whole thing is in a broken down list you could use the cell reference for each of the criteria per row to really simplify it. That would also take into account if you need to change anything in the reference cells without having to go back and edit the reference in the formula for typos, name changes, etc.

 

Name Column            Status Column            Type Column       Formula Column

Mike                            Open                           Good

Mike                            Closed                         Good

John                            Open                            Bad

John                            Closed                          Bad

 

=COUNTIFS({Assigned To - BD}, [Name Column]@row, {Implementation Status - BD}, [Status Column]@row, {Work Type - BD}, [Type Column]@row)

 

If you drag fill that down the Formula Column you would get the count of how many times that exact set of Name/Status/Type occurs on your reference sheet.

Sean Taber

Hi Paul,

Thank you for getting back to me.  So I referenced the cell instead of hard coding the name.  That works.  The only thing is when I drag, the cell in the formula moves down one.  So if the cell was Name1, I drag to the next cell and the formula changes to Name2, then Name3.  So then I have to update that part of the formula from Name2 and Name3 to Name1.  Is there a way around this?

In regards to your other piece, I'm not fully understanding it.  In my example, I'm referencing another sheet so Assigned To - BD, Implementation Status - BD and Work type -BD are reference names.  These reference names are the columns.  How would I also specify an additional column in the formula.  You wrote 

=COUNTIFS({Assigned To - BD},[Name Column]@row....then [Status Column]@row and lastly [Type Column]@row.

What are the column names in front of @ row?

I updated the format of my grid.  I attached a new view of it.  This format seemed to work better for what I was trying to do.

Thank you,

Sean

Sean Taber

Hi Paul,

I'm not sure what happened.  I replied but it didn't go through.  So take 2.

So I did reference the cell instead of hard coding the specific name.  However when I dragged the formula down the formula increased by 1.  So I had to update the name from Assigned To-BD3 back to Assigned to - BD1 and each of the other cells that I increased by 1 by dragging.  How do I set it so the formula doesn't change for that piece.  I did want the Work Type to increase by 1.

I'm not sure I understand your second piece.  You wrote:  =COUNTIFS({Assigned To - BD}, [Name Column]@row, {Implementation Status - BD}, [Status Column]@row, {Work Type - BD}, [Type Column]@row).

({Assigned To - BD}, {Implementation Status - BD}, and {Work Type - BD} are reference names which I'm using as columns.  How can I also include [Name Column]@row, [Status Column]@row, and [Type Column]@row) since they are columns too?

Sean

J. Craig Williams

Staber, I saw your reply come through. Earlier this morning I had a post that was not shown due to "queued for review". Shaine needed to release it for public consumption. I don't know if that is what happened to yours.

Craig

 

Sean Taber

ahhh, that must have been it.

Paul Newcome

The screenshot in your original post... Is that your reference sheet or your target sheet? Would you be able to provide a screenshot of the other sheet as well? I don't know how, but I missed the screenshot when I made my last post. Taking another look at everything I do believe I see what exactly it is you are trying to do (hopefully maybe).

 

The name you are referencing appears to be the parent row in the OCA / Client Type column. If this is correct you can use:

 

=COUNTIFS({Assigned To - BD}, PARENT([OCA / Client Type]@row),

 

This will always reference the parent cell of whatever row you happen to be in which contains the name you are looking for.

 

It also appears (based on your screenshot) that your work type is listed down in rows in which case you could use another @row reference to keep from having to type out all 14 of them. For that you would replace

 

{Work Type - BD}, "Advanced Billing"

 

with:

 

@{Work Type - BD}, [OCA / Client Type]@row

 

That would look reference whatever work type happens to be in that row which. Putting the two together will look at the parent row for reference of the name and look at the row the formula is in for reference of the work type.

 

From there all that is missing would be your Implementation Status which could also be plugged in with the @row function if I am understanding more correctly what it is you're trying to accomplish.

 

I am also assuming you are trying to save yourself some time from having to change each formula for each variation, thus the reason for the PARENT reference and the @row reference.

If you can get it to look at the sheet based off of where the formula is, then it will automatically pull the data you need. If you can get it so you can plug the exact same formula into any row and it still works exactly as needed... I imagine that would be ideal.

Sean Taber

I switched gears a little bit.  My original screen shot was just another variation.  I was trying to determine which format to use.  The work types going across or the Imp Statuses.  I went with the Imp Statuses because it seemed easier to look at and easier to apply formulas.  So the second screen shot above is the format I'm using.  Neither where the reference sheets though.  They were the target sheets.  The reference sheets are trackers where all the details of our implementations are stored.  More than what you are seeing here.  I attached a screen shot.  

Good news though!!!  The formula worked for the Assigned To using Parent and @row, and Work type using @row.  The Imp Status might be hard to use @row for.  I don't have a row I can reference.  Might be best to leave it as is ("Pending Kick Off", "Pre Live...etc).  

Question though.  So when I drag the formula to the right, everything in the formula shifts too.  So it's now looking at the info in the next column.  Here is an example:  =COUNTIFS({Assigned To - BD}, PARENT([Pending Kick Off]@row), {Implementation Status - BD}, "Pending Kick Off", {Work Type}, [Pending Kick Off]@row)

You can see that after Parent and Work Type, it's now Pending Kick Off which is the next column over from OCA / Client Type.  Is there anyway around that or you only can drag the formula down for it to work right?

Paul Newcome

Glad we've gotten part of it working so far! 

 

I am starting to see better now what it is you're trying to do and the layout of everything.

 

Locking in the OCA / Client Type column is actually very easy. Simply add a "$" before the column name and it's locked (the same can be done with row references as well).

 

So 

PARENT([OCA / Client Type]@row)

changes to

PARENT($[OCA / Client Type]@row)

 

And leaving the Imp status as "Defined text" isn't too bad since there are only 4 and they are split down as their own column. Write 1 formula up for each type in the corresponding column, and dragfill on down.

 

This should pretty much do it for you... *fingers crossed*

Sean Taber

Works Awesome!!!  Thank you!!

Paul Newcome

I'll go ahead and throw this out there too...

 

=SUM(CHILDREN())

 

Put in the parent row of each column will give you your totals for each Imp Status. Kind of an overview for each phase for each person.