SUMIFS & FIND functionality with Summary Sheets

CL-

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!

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.