Formula Writing-COUNTIFS & SUMIFS

asisneros84456

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

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

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?

 

 

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.