SUMIFS & FIND functionality with Summary Sheets

Hi All, I checked the archives and couldn't find a solution to my problem so I apologize if this has already been asked.

I am trying to create a formula for my Summary Sheet that will provide the sum of dollar amounts but only if they are "Cost Savings" or "Risk Management". I am also trying to create a formula for just the sum of "Revenue Generating" in my Summary Sheet.

I've tried formulas

=SUMIFS({PRODUCT DEVELOPMENT FUNNEL Range 7}, >0, FIND({PRODUCT DEVELOPMENT FUNNEL Range 3}, ="Cost Savings", [Reason]:[Reason]))

and

=SUMIFS({PRODUCT DEVELOPMENT FUNNEL Range 7}, {PRODUCT DEVELOPMENT FUNNEL Range 7}, FIND({PRODUCT DEVELOPMENT FUNNEL Range 3}, ="Risk Management") > 0)

Neither are giving me the correct total.Thanks for your help!

Find more posts tagged with

Forms

Manufacturing

Formulas

Reports

Product Development

Comments

Alejandra

Hi CL,

If your goal is to sum the dollar amount for rows where the reason is either "Cost Savings" or "Risk Management" —you'll want to try something like this:

Cost Savings:

=SUMIF(Reason:Reason, "Cost Savings", [Dollar Amount]:[Dollar Amount])

Risk Management:

=SUMIF(Reason:Reason, "Risk Management", [Dollar Amount]:[Dollar Amount])

If needed, more information on the SUMIF syntax can be found here: https://help.smartsheet.com/function/sumif

Barry Bowles

Hi CL - I'm not sure if you a have resolved your question but Alejandra has given you the solution for summing an individual reason such as "Cost Reduction" or "Revenue Generating" but if you wanted to sum the total if they are "Cost Savings" or "Risk Management" in one formula you can use the following formula:

=SUMIFS([Project Value]:[Project Value], Reason:Reason, OR(FIND("Cost Reduction", @cell) > 0, FIND("Risk Management", @cell) > 0))

Use the simpler SUMIF if you only want to solve for one criteria and SUMIFS if you want to solve for multiple criterion.

Hope this helps.

Sinema

thanks, that worked like a charm.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.