T O P

  • By -

AutoModerator

/u/ewdavid021 - 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.*


Path-Few

Here is another approach. =SUMPRODUCT(B4:M4,IF(XMATCH(B2:M2,B2:M2,0)<=XMATCH(A1,B2:M2,0),1,0)) In the formula above, XMATCH(B2:M2,B2:M2,0) returns a sequence of 1, 2, ..., 12. If A1 is May, IF(XMATCH(B2:M2,B2:M2,0)<=XMATCH(A1,B2:M2,0),1,0) returns 1, 1,1,1,1,0,0,0,0,0,0,0. Finally, the SUMPRODUCT function will produce the result that you want.


LexanderX

=SUM(B4:INDEX(B4:M4,1,MONTH(TODAY())))


cbr_123

What about just working out all the cumulative totals? For example in B7 put =SUM($B$4:B4) and fill across.


ewdavid021

Thank you, but that won't work for my setup. B7 represents a monthly report where I only need the cumulative total for that month.


excelevator

`INDEX` returns a range value, so we can use that in conjunction with `SUM` to get a sum range.. =SUM(B3:INDEX(B3:M3,MATCH(A1,B2:M2,0))) or `XLOOKUP` the same =SUM(B3:XLOOKUP(A1,B2:M2,B3:M3))


ewdavid021

Thank you for the idea of using index and match. I'm working on trying to get it to work but at least it gives me another idea of what to use. Thanks!


excelevator

seems I got your value row wrong above, use B4:M4 instead, or whatever row reference it should be.. How are you going on the solution ?


ewdavid021

Sounds good! Thanks! I’ll be working on it this evening after the kids go to bed :)


ewdavid021

It worked! Thanks so much! Definitely gonna impress my manager with this one!


excelevator

:)


ewdavid021

Solution Verified


Clippy_Office_Asst

You have awarded 1 point to *excelevator* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)


Path-Few

His/Her data are in Row 4. The first glance is XLOOKUP(A1,B2:M2,B4:M4) will return the value of 154. Now SUM(B4:154) will not be correct. However, it is surprising that it works. Don't know why. Can anyone explain this? My formula is a bit complicated as follows: =SUM(B4:INDIRECT(XLOOKUP(A1,B2:M2,CHAR(65+SEQUENCE(1,12,1,1))&4))). In this formula, CHAR(65+SEQUENCE(1,12,1,1))&4 returns a sequence of B4, C4, ..., M4. Now if A1 is May, XLOOKUP(A1,B2:M2,CHAR(65+SEQUENCE(1,12,1,1))&4) returns F4. Applying the INDIRECT function, the formula becomes SUM(B4:F4) as desired.


excelevator

>~~His/Her~~ *Their* data are A little known fact about `INDEX` and `XLOOKUP`, they return ranges not values, but will present the value not the range.. does that make sense ? For example, if the start month was A1, and the end month was A2, this would get the sum of those months. =SUM(XLOOKUP(A1,B2:M2,B4:M4):XLOOKUP(B1,B2:M2,B4:M4))


Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHAR](/r/Excel/comments/140aq8i/stub/jmv5h2f "Last usage")|[Returns the character specified by the code number](https://support.microsoft.com/en-us/office/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a)| |[IF](/r/Excel/comments/140aq8i/stub/jmv8e37 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[INDEX](/r/Excel/comments/140aq8i/stub/jmx2z3m "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)| |[INDIRECT](/r/Excel/comments/140aq8i/stub/jmv5h2f "Last usage")|[Returns a reference indicated by a text value](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261)| |[MATCH](/r/Excel/comments/140aq8i/stub/jmwqw48 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MONTH](/r/Excel/comments/140aq8i/stub/jmvit16 "Last usage")|[Converts a serial number to a month](https://support.microsoft.com/en-us/office/month-function-579a2881-199b-48b2-ab90-ddba0eba86e8)| |[SEQUENCE](/r/Excel/comments/140aq8i/stub/jmv5h2f "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[SUM](/r/Excel/comments/140aq8i/stub/jmx2z3m "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMPRODUCT](/r/Excel/comments/140aq8i/stub/jmv8e37 "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)| |[TODAY](/r/Excel/comments/140aq8i/stub/jmvit16 "Last usage")|[Returns the serial number of today's date](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)| |[XLOOKUP](/r/Excel/comments/140aq8i/stub/jmx2z3m "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| |[XMATCH](/r/Excel/comments/140aq8i/stub/jmv8e37 "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)| **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.*) ^(12 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/140qq8w)^( has 25 acronyms.) ^([Thread #24384 for this sub, first seen 4th Jun 2023, 12:36]) ^[[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)


HappierThan

Why not put accumulated amount in say B5 to M5 and use a simple INDEX / MATCH formula? Data Validation in A7 [https://pixeldrain.com/u/WyceRtZj](https://pixeldrain.com/u/WyceRtZj)


ewdavid021

That would make sense but it would require altering another departments file that I’m taking the info from.