Formula Writing-COUNTIFS & SUMIFS

Hello,

I am trying to get a couple formulas to work to filter down a roll up sheet. I have tried the following formulas to no avail:

=COUNTIFS(Phase 3:47, = "Leads", [ Phase 3:47, = "Closed"], [Phase 3: 47, = "In-Service"], [Phase 3:47, = "Closed"])

=SUMIFS(Total Project Cost(), Phase()="Leads")

Let me know if there is another resource to help with formatting of these, I am not getting the basic layouts provided. It seems like there are spaces I am missing that have hung up my other formulas.

Thanks,

Amy

Find more posts tagged with

Formulas

Comments

Paul Newcome

Give these a try...

=COUNTIFS(Phase3:Phase47, OR(@cell = "Leads", @cell = "Closed", @cell = "In-Service))

=SUMIFS([Total Project Cost]:[Total Project Cost], Phase:Phase, @cell = "Leads")

asisneros84456

These did not work either. Do you have other suggestions?

Thanks,

Amy

Paul Newcome

Are you able to provide some screenshots with sensitive data removed?

asisneros84456

Attached are a couple views of the pipeline sheet. I am essentially trying to filter by phase and have the system show me the total project costs. I may need to add another filter by region but this is a roll up sheet that pulls in data from other sheets broken out by region.

Thanks,

Amy

Pipeline Formula Issues.PNG

Pipeline Formula view 2.PNG

Paul Newcome

I am not sure why the COUNTIFS in the Phase column isn't working. Did you retype the formula I provided exactly?

The SUMIFS is because you are referencing the formula cell within the range. You will need to specify the range for the SUMIFS to cover the same rows 3 - 47.

asisneros84456

I copied them from your message.

Should the COUNTIF have an "OR" inside it? It's showing as unparseable with the formula as typed. On the SUMIFS, it shows as blocked after typing it in. If I adjust the cells it says circular reference. I have totals in the gray sections under each region. Do I need to eliminate those from the calculations?

Project Pipeline Formula view 3.PNG

Paul Newcome

The COUNTIFS with the OR is fine. I used that because your original formula made it look like you were trying to count all 3 Phases together. The reason for the #UNPARSEABLE error was because I had missed a closing quote (I do that to myself all the time too ugh). Corrected is below.

=COUNTIFS(Phase3:Phase47, OR(@cell = "Leads", @cell = "Closed", @cell = "In-Service"))

Try this for the SUMIFS. It should work.

=SUMIFS([Total Project Cost]3:[Total Project Cost]47, Phase3:Phase47, @cell = "Leads")

asisneros84456

Thanks those all work.

I think I am looking to see if I can do a drop down that allows me to get the total by phase and total project cost. I saw a training in the Center for Excellence by Oliva S. called Save Time & Increase Efficiency with Formulas but have yet to find a handout or other resource online to help me write the formulas. Do you have any suggestions where to look?

Andrée Starå

Hi Amy,

I might have something.

Email me @ andree@getdone.se and I'll share what I have.

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

So you are looking for the totals to be in one row and for them to adjust based on a dropdown?

asisneros84456

Correct. This other version works too but it would be of benefit to use a drop down.

Paul Newcome

Ok. Next question...

Will this be a summary row on the SAME sheet or on a different sheet?

asisneros84456

Sorry for the delay this would be on the same sheet.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…