Sum Column Untill Criteria is met AND Repeat Formula

Johnhtvu

New Member
Joined
Feb 25, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. MacOS
Hi everyone - I have been trying all day but I can't seem to find a way to make my formula repeat after a criteria is met. Please see screenshot.

Situation:
I have a table. in column [Cogs Value] you see prices of ingredients that I want to sum. in column [subrecipe name] you can enter any text and the row will turn yellow to indicate a new recipe. in column [subrecipe cost] I want to sum the cost of all ingredients that belong to that recipe.

Problem:
What formula can I use in the [subrecipe cost] column? The formula should be able to:
1 stop summing once any value in column [subrecipe name] is made
2 Start the formula again to sum up for the new recipe

Potential solution/ ideas:
I was thinking to somehow use the formulas:
- Sumifs (with criteria that column [subrecipe name] contains any text.
- What other formula to add so I can repeat the formula once that criteria is met?

Help is much appreciated!
Thank you everyone (sorry if I did not explain it clearly)
 

Attachments

  • Check formula.png
    Check formula.png
    125.9 KB · Views: 6

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
We are not able to test formula on screen captures, but I think that this should work. Enter the formula into E8, then fill down.
Excel Formula:
=IF(C8="","",SUM(J9:J$26)-SUM(E9:E$26))
As we don't know which columns the relevant items of data are located in, I've assumed C, E and J by counting the visible columns and assuming that none are merged.
 

Johnhtvu

New Member
Joined
Feb 25, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. MacOS
We are not able to test formula on screen captures, but I think that this should work. Enter the formula into E8, then fill down.
Excel Formula:
=IF(C8="","",SUM(J9:J$26)-SUM(E9:E$26))
As we don't know which columns the relevant items of data are located in, I've assumed C, E and J by counting the visible columns and assuming that none are merged.
Hi Jason

This worked!! Thank you for taking the time to reply to my question. I appreciate it!

John
 

Watch MrExcel Video

Forum statistics

Threads
1,127,803
Messages
5,626,997
Members
416,214
Latest member
boston814

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top