SUMIFS & FIND functionality with Summary Sheets

CL-
CL-
edited 12/09/19 in Formulas and Functions

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!

Capture.JPG

Capture1.JPG

Comments

  • Alejandra
    Alejandra Employee
    edited 07/29/19

    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

  • 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.

  • thanks, that worked like a charm.