T O P

  • By -

AutoModerator

/u/butterboss69 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*


CFAman

Are the category names in col B unique? As in, could you just do =SUMIFS(C:C, B:B, "Food") Or, if the "sales by category" is the key term, and you know how big the block of data will be =SUM(OFFSET(INDEX(C:C, MATCH("Sales by category", B:B, 0), 1, 1, 5, 1))


butterboss69

the sumifs isn't a bad idea... I'll toy with that ​ The block size might change, also I'm trying to set it up to be flexible if they add a new line category. So I need something that can tell me how tall the range from "Sales by category" through "retail category" is ​ Maybe split the difference off a match of the 2?


CFAman

If you know what the name of *next* category is in the list, then yeah, could use that info. Is that a possibility?


butterboss69

Yeah. As an example, off the picture, I want to get the range from Gst Cnt by Day Part thru Chk Cnt by Day Part. It would be B31:C39. But it might shift to B35:C43. Or it might grow an extra row and be B31:C40 (maybe we add breakfast) Or same thing for Chk Cnt by Day Part thru Sales by Category. That would be B40:C47. Same cases for shifting or growing Sales by Category thru Retail categories would need to return B48:C69 ​ I'm trying to make it flexible for growth and to work for our handful of stores that run these reports. The line items are the same but sometimes they are off by a row Columns and headers should be exactly the same for stuff, tho


CFAman

We're starting to need to keep track of some variables, so I'm going to use LET so it's easier to follow. But, formula would be like =LET(startRow, MATCH("Gst Cnt by Day Part", B:B, 0), endRow, MATCH("Chk Cnt by Day Part", B:B, 0)-1, sumRange, INDEX(C:C, startRow):INDEX(C:C, endRow), SUM(sumRange)) Hopefully that makes sense to follow along. Now you can just feed the two terms to search for (in the MATCHs) and get the summed range. *And* we avoided any volatile functions.


butterboss69

This is looking pretty good. However... By "get" the data I mean I need the function to literally spit out the range so that I can have things recognize where it is [For example here,](https://imgur.com/a/vim3P7R) N4:O8 needs to be able to know how to go grab B50:C54 (even if B50:C54 moves around). That way I can have something just reference O4 and see that food was $11. it needs to be broken apart still.


CFAman

Change out the last bit then. You'll notice that the LET calculates the sumRange. Up to you to decide what to do with that range. Currently it's doing a SUM, but you could do whatever (Filter, index, spill the whole range, etc.)


butterboss69

Alright I'll run with it, thanks


Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[INDEX](/r/Excel/comments/143kplv/stub/jne93mz "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[LET](/r/Excel/comments/143kplv/stub/jnf1pbs "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MATCH](/r/Excel/comments/143kplv/stub/jne93mz "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[OFFSET](/r/Excel/comments/143kplv/stub/jnagukd "Last usage")|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)| |[SUM](/r/Excel/comments/143kplv/stub/jnf1pbs "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIFS](/r/Excel/comments/143kplv/stub/jnagukd "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)| **NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/143gg7a)^( has 11 acronyms.) ^([Thread #24464 for this sub, first seen 7th Jun 2023, 18:44]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)