SUMIF condition is met

I have a billing projections sheet that I am trying to figure out how to sum (total) the Current Year to Date billing of a project manager. I have a Project Manager column and Billing CYD column.

Here is the formula I have so far...

=SUMIF([Project Manager], Name of Project Manager, [Billing CYD])

Obviously this does not work but I want the formula to sum only the Billiing CYD cells that have a specific project manager assigned to them. So, for example, if Joy has six projects the formula will look at all six corresponding cells on her row for "Billing CYD". Please help.

Find more posts tagged with

Comments

Dave D

You were close!

Here is the syntax for SUMIF formulas:

SUMIF(criteria_range, criteria_value, sum_range)

You need to set a range of cells for criteria and sum. [Project Manager] and [Billing CYD] are column names, not ranges. You will also need quotes around the text you are looking for.

Try this:

=SUMIF([Project Manager]:[Project Manager] , "Name of Project Manager," [Billing CYD]:[Billing CYD])

Saylor

Hot dog! It worked...thank you so much!

Brett Evans

love that he said hot dog

J. Craig Williams

LOL.

Jesse Drew

Hello,

What would the formula be if I wanted to add another layer of complexity to this based on product type (ex: 2 options - Product 1 or Product 2) . Would I add it after the "Name of Project Manager" value? Thank you in advance for your help.

=SUMIF([Project Manager]:[Project Manager] , "Name of Project Manager," [Product Type]:[Product Type] , "Product 1," [Billing CYD]:[Billing CYD])

FredAshley

Hey Jessie,

Try this way. I think you have to use the SUMIFS formula and switch the arguments around if you are using multiple conditions.

=SUMIFS([Billing CYD]:[Billing CYD], [Project Manager]:[Project Manager] , "Name of Project Manager," [Product Type]:[Product Type] , "Product 1,")

ZebraCat

I am having a problem trying to use this formula. i am trying to track allocations of my copywriter's within our project sheet. Below is my formula:

=SUMIF([CW]7:[CW]160,"Copy," [CW Hrs]7:[CW Hrs]160)

CW = the column my copywriters name's appear.

"Copy" is the name of my copywriter

CW Hrs = the hours of allocated for the project.

What am I doing wrong?

J. Craig Williams

You misplaced a quotation mark.

=SUMIF([CW]7:[CW]160, "Copy" , [CW Hrs]7:[CW Hrs]160)

Craig

ZebraCat

That fixed it! Thank you!

AnneMarie

Brand new to Smartsheets and trying to create a formula that will sum a sales reps projects that have a >50% probability.

So I took a shot and wrote SUMIF([Rep]:[Rep],"Jack," [Probability]:[Probability], > "50%" [Quoted Price]:[Quoted Price])

It didn't work. I haven't see this type of formula yet so I need some help please.

AnneMarie

I would also like help with a formula to sort sums by rep, >50, & close date ranges

J. Craig Williams

AnneMarie,

I'll start with a correct formula for your first post

=SUMIFS([Quoted Price]:[Quoted Price], Rep:Rep, "Jack", Probability:Probability, >0.5)

And then the explanation:

1. For more than one criteria, you MUST use SUMIFS, not SUMIF. I have stopped using SUMIF (nearly) completely because SUMIFS also works with only one criterion. Note that [Quoted Price] is moved to the first argument for SUMIFS.

2. When looking at percentages, Smartsheet using 0=0% and 1=100%. They may display at 100%, but they are really a Number, not text.

When you put "50" or "50%", you are telling Smartsheet you are comparing Text, so it gets confused (but acts like it isn't)

And back to your original attempt, you missed a few syntax nuances

Formulas takes arguments separated by commas (semi-commas if you personal settings are set to a country that swaps the US conventions of comma/periods)

You had "Jack , " instead of "Jack" ,

and no comma after the "50%". Don't get discouraged, it isn't as hard as it may appear at first glance.

For your second post, just add another criterion to the formula above

=SUMIFS([Quoted Price]:[Quoted Price], Rep:Rep, "Jack", Probability:Probability, >0.5, [CLOSEDATE]:[CLOSEDATE], CRITERION)

Oh, and one more thing. Older posts may not be followed by people. You are better off posting a new threads and referencing this one (with the URL). More people will see it and you are likely to get a quicker response.

Welcome to the Community and Welcome to Smartsheet.

I hope that helps.

Craig

AnneMarie

Thank you.

chinnette.cannida34906

I’m trying to count the number of activities in the ACTIVITY column that will be selected from the ACTIVITY dropdown list. I want the totals to show next to the Total Field, Total Content and Total Viral cells at the top of the sheet. I've tried to use the SUMIF formulas, but I continue getting error messages.

Tom deGroh

I am facing a similar issue and this doesn't seem to work. I am trying to set up I think is similar. I have a name column and a steps column. I want a sum of all steps made by a person identified in the name column. I keep getting either unparseable or invalid argument.

FYI this is how Smartsheet formats it after I put in brackets, etc.

=SUMIF(Name:Name, "tom", Steps1:Steps64)