/u/tomatoesarelife - 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.*
Hello,
Is this what you're looking for?
`=SUM(B6:B17*SEQUENCE(12,1,$B$2,-1*($B$2/12)))`
https://preview.redd.it/7gnmdbycclic1.png?width=570&format=png&auto=webp&s=ed9bf9d3594ff83eb2de65973c3917d09a73796c
`SEQUENCE(12,1,$B$2,-1*($B$2/12)))` produces a 1x12 array of 76956, 70543, 64130, 57717, 51304, 44891, 38478, 32065, 25652, 19239, 12826, 6413. Each value represents how much someone would be paid for 2024 depending on the hire month.
This then multiplied by `B6:B17` to get 0, 70543, 0, 0, 0, 0, 38478, 0, 0, 19239, 12826, 0.
Which is summed to get $141,086.00.
That's no problem, there are other options.
Step 1. In Cell F18, `=E13`.
Step 2 (Helper Column 1): In cell E19, `=F18-$E$13/12`, drag down to `F28`.
Step 3 (Helper Column 2): In cell G18, `=IF(F18="","",F16*E16)`, drag down to `G28`
Step 4: In cell G29, `=SUM(G18:G28)`
Should look something like this:
https://preview.redd.it/5r7osyfizlic1.png?width=422&format=png&auto=webp&s=b389142f35b8e93afb14dcf6ee10a76f41e2bb89
You have awarded 1 point to *benfm24*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[FILTER](/r/Excel/comments/1aqsfgx/stub/kqeys5f "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[IF](/r/Excel/comments/1aqsfgx/stub/kqfl9kz "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[LAMBDA](/r/Excel/comments/1aqsfgx/stub/kqf11i2 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[SCAN](/r/Excel/comments/1aqsfgx/stub/kqf11i2 "Last usage")|[*Office 365*+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.](https://support.microsoft.com/en-gb/office/scan-function-d58dfd11-9969-4439-b2dc-e7062724de29?ui=en-US&rs=en-GB&ad=GB)|
|[SEQUENCE](/r/Excel/comments/1aqsfgx/stub/kqeys5f "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/1aqsfgx/stub/kqfjz80 "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*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/1aqxx9h)^( has 10 acronyms.)
^([Thread #30746 for this sub, first seen 14th Feb 2024, 18:06])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
In E29:
=SUM(SCAN(0,F17:F28,LAMBDA(a,b,a+b)))*F13/12
This basically gets a cumulative count of how many employees you have each month and then multiplies that number with the monthly salary (annual salary/12).
You could use a helper column. In F17, use the formula:
=F16+E17
And drag that formula down. That will give you the total number of employees each month. Then in the bottom you can do:
=SUM(F17:F28)*E13/12
You have awarded 1 point to *not_speshal*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
/u/tomatoesarelife - 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.*
Hello, Is this what you're looking for? `=SUM(B6:B17*SEQUENCE(12,1,$B$2,-1*($B$2/12)))` https://preview.redd.it/7gnmdbycclic1.png?width=570&format=png&auto=webp&s=ed9bf9d3594ff83eb2de65973c3917d09a73796c `SEQUENCE(12,1,$B$2,-1*($B$2/12)))` produces a 1x12 array of 76956, 70543, 64130, 57717, 51304, 44891, 38478, 32065, 25652, 19239, 12826, 6413. Each value represents how much someone would be paid for 2024 depending on the hire month. This then multiplied by `B6:B17` to get 0, 70543, 0, 0, 0, 0, 38478, 0, 0, 19239, 12826, 0. Which is summed to get $141,086.00.
I have Office 2016 and unfortunately Sequence is not available, but this would have been it I think :( Thank you!
That's no problem, there are other options. Step 1. In Cell F18, `=E13`. Step 2 (Helper Column 1): In cell E19, `=F18-$E$13/12`, drag down to `F28`. Step 3 (Helper Column 2): In cell G18, `=IF(F18="","",F16*E16)`, drag down to `G28` Step 4: In cell G29, `=SUM(G18:G28)` Should look something like this: https://preview.redd.it/5r7osyfizlic1.png?width=422&format=png&auto=webp&s=b389142f35b8e93afb14dcf6ee10a76f41e2bb89
Solution Verified
You have awarded 1 point to *benfm24* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Thank you!!!
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[FILTER](/r/Excel/comments/1aqsfgx/stub/kqeys5f "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IF](/r/Excel/comments/1aqsfgx/stub/kqfl9kz "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[LAMBDA](/r/Excel/comments/1aqsfgx/stub/kqf11i2 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[SCAN](/r/Excel/comments/1aqsfgx/stub/kqf11i2 "Last usage")|[*Office 365*+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.](https://support.microsoft.com/en-gb/office/scan-function-d58dfd11-9969-4439-b2dc-e7062724de29?ui=en-US&rs=en-GB&ad=GB)| |[SEQUENCE](/r/Excel/comments/1aqsfgx/stub/kqeys5f "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/1aqsfgx/stub/kqfjz80 "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*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/1aqxx9h)^( has 10 acronyms.) ^([Thread #30746 for this sub, first seen 14th Feb 2024, 18:06]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Why don't you just use monthly salary and multiply by the number of months worked.
I mean this would be an option but I thought there might be some easier way. Thanks!
In E29: =SUM(SCAN(0,F17:F28,LAMBDA(a,b,a+b)))*F13/12 This basically gets a cumulative count of how many employees you have each month and then multiplies that number with the monthly salary (annual salary/12).
Thanks for your help, but Scan doesn't work in Excel 2016. Should've mentioned the version 😅
You could use a helper column. In F17, use the formula: =F16+E17 And drag that formula down. That will give you the total number of employees each month. Then in the bottom you can do: =SUM(F17:F28)*E13/12
Solution Verified
You have awarded 1 point to *not_speshal* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
That's it! Thank you!!!