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

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…