+ Reply to Thread
Results 1 to 19 of 19

Formula to count/sum number of cells needed to match value

  1. #1
    Registered User
    Join Date
    12-10-2019
    Location
    Whistler, Canada
    MS-Off Ver
    2010
    Posts
    9

    Formula to count/sum number of cells needed to match value

    Hi, any help appreciated here.

    I would like a formula that can count how many cells it takes before a set value is met but not exceeded.
    This will be used for inventory purposes to show how many weeks of stock we have on hand.

    The attachment shows 8 weeks of forecasted sales ( B3:I3) and the number of units we have on hand (C8).
    I would like, in D8, a formula that shows the number of weeks of inventory we have.


    In our real spreadsheet we have weeks and weeks of forecasted sales.
    So for bonus points, could the formula that counts the number of weeks change its starting count point based off an additional second cell?
    IE if we are in week 2 of December I would like the formula to only count from week 2.

    I hope I have explained this properly.
    Thanks again
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula to count/sum number of cells needed to match value

    I'm intrigued by the bonus points and how they will be awarded.
    Anyway, will the actual workbook include the manual calc row (row 4) or is that just there so we can see how you are basing your answer?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    12-10-2019
    Location
    Whistler, Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Formula to count/sum number of cells needed to match value

    Haha I'm sure I can work something out

    The manual calc row is just to show the answer.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula to count/sum number of cells needed to match value

    I can get you there but it relies on that manual calculation row, everytime I try to recreate it in the formula it blows up.
    I'll keep playing with different versions of formulas, hopefully someone will come along with a quick answer in the mean time.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula to count/sum number of cells needed to match value

    Please try in D8
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I am still not sure what the required answer in D11 would be. So I will hazard a guess. It returns 5 for start in WK1.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit Even if I have interpreted correctly the D11 cell that formula will probably require some modifications.

    Another Edit Sam caught an oversight of mine (see below). Those need to be entered as array formulas. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Last edited by FlameRetired; 12-11-2019 at 12:47 PM.
    Dave

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula to count/sum number of cells needed to match value

    Dave, aren't yours array formulas? I find they return #N/A unless I activate them with ctrl+shift+enter.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula to count/sum number of cells needed to match value

    @ Sam
    Thank you. Yes. That was an oversight on my part. The 'Insider' 365 thing masked that and I forgot.

  8. #8
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula to count/sum number of cells needed to match value

    Another way (array-entered)

    =MATCH(C8,MMULT(B3:I3,--(COLUMN(B3:I3)>=TRANSPOSE(COLUMN(B3:I3)))),1)


    Not sure I understand the bonus points question!
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  9. #9
    Registered User
    Join Date
    12-10-2019
    Location
    Whistler, Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Formula to count/sum number of cells needed to match value

    FlameRetired
    Thank you for taking the time. It works for me as an array.

    Would there be a way to not use the manual calculation?


    In regards to D11, I don't think I explained it very well.
    I was hoping for the D8 formula to reference the D11 cell so to understand where to start the count from.


    This would allow us to change the D11 cell each week to update the formula. Rather than going in and changing the formula each week.


    Thanks,
    Alex

  10. #10
    Registered User
    Join Date
    12-10-2019
    Location
    Whistler, Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Formula to count/sum number of cells needed to match value

    Ace_XL

    Thats great thank you.

    The bonus question:
    The idea was that I could change the D11 cell each week and it would update the formula to count from the correct week.

    Ie This week we are in December WK2. So if I were able to change one cell rather than change the formula it would be amazing.
    This formula is going to apply to over 50 lines of products. It would be nice to not have to change 50 formulas each week.

    Thanks,
    Alex

  11. #11
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula to count/sum number of cells needed to match value

    Use this modified version in Cell E11

    =MATCH(C8,MMULT($I3:INDEX($B$3:$I$3,MATCH($D$11,$B$2:$I$2,0)),--(COLUMN($I3:INDEX($B$3:$I$3,MATCH($D$11,$B$2:$I$2,0)))>=TRANSPOSE(COLUMN($I3:INDEX($B$3:$I$3,MATCH($D$11,$B$2:$I$2,0)))))),1)


    Array entered

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula to count/sum number of cells needed to match value

    Quote Originally Posted by Alex Q View Post
    FlameRetired
    Thank you for taking the time. It works for me as an array.

    Would there be a way to not use the manual calculation?


    In regards to D11, I don't think I explained it very well.
    I was hoping for the D8 formula to reference the D11 cell so to understand where to start the count from.


    This would allow us to change the D11 cell each week to update the formula. Rather than going in and changing the formula each week.


    Thanks,
    Alex
    It sounds like you want two methods for calculating the 'Actual Weeks OH' in D8. The first one returns 6 as indicated.

    I was hoping for the D8 formula to reference the D11 cell so to understand where to start the count from.
    That is what I was attempting with the second formula pending your confirmation that it worked. As it stands these are the returns for each of the December WK#s: (Please confirm that these are correct so I can proceed with the second method ... referencing D11 ...)


    December WK 1
    5
    December WK 2
    4
    December WK 3
    3
    December WK 4
    2



    To replace the manual calculation in D18 try array entering this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 12-12-2019 at 12:11 AM.

  13. #13
    Registered User
    Join Date
    12-10-2019
    Location
    Whistler, Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Formula to count/sum number of cells needed to match value

    Quote Originally Posted by FlameRetired View Post
    : (Please confirm that these are correct so I can proceed with the second method ... referencing D11 ...)


    December WK 1
    5
    December WK 2
    4
    December WK 3
    3
    December WK 4
    2



    [/FORMULA]
    Sorry for the delay in replying.

    Those results are correct only based on the Manual Calc row.
    I think the problem here is that "Manual Calc" row counts from December WK 1. So changing the start week doesn't actually change anything in D11 unless it was using the "Forecasted Sales" row.

    Alex

  14. #14
    Registered User
    Join Date
    12-10-2019
    Location
    Whistler, Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Formula to count/sum number of cells needed to match value

    Quote Originally Posted by Ace_XL View Post
    Use this modified version in Cell E11

    =MATCH(C8,MMULT($I3:INDEX($B$3:$I$3,MATCH($D$11,$B$2:$I$2,0)),--(COLUMN($I3:INDEX($B$3:$I$3,MATCH($D$11,$B$2:$I$2,0)))>=TRANSPOSE(COLUMN($I3:INDEX($B$3:$I$3,MATCH($D$11,$B$2:$I$2,0)))))),1)


    Array entered
    Thank you.

  15. #15
    Registered User
    Join Date
    12-10-2019
    Location
    Whistler, Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Formula to count/sum number of cells needed to match value

    Quote Originally Posted by Ace_XL View Post
    Use this modified version in Cell E11

    =MATCH(C8,MMULT($I3:INDEX($B$3:$I$3,MATCH($D$11,$B$2:$I$2,0)),--(COLUMN($I3:INDEX($B$3:$I$3,MATCH($D$11,$B$2:$I$2,0)))>=TRANSPOSE(COLUMN($I3:INDEX($B$3:$I$3,MATCH($D$11,$B$2:$I$2,0)))))),1)


    Array entered
    I'm trying to implement your formula on our spreadsheet and i'm having some difficulties. I suspect its something simple - but as I don't really understand how your formula works its hard to trouble shoot.

    I have attached a minimalist version of our spreadsheet. Would you please be able to tell me what I have done wrong?
    G2 is a drop down list for reference of what "Week"
    G4 is your formula
    Attached Files Attached Files

  16. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula to count/sum number of cells needed to match value

    It sounds like your question has been answered.
    If that's true, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  17. #17
    Registered User
    Join Date
    12-10-2019
    Location
    Whistler, Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Formula to count/sum number of cells needed to match value

    We are almost there - thank you for all the help so far.

    I will mark it Solved as soon as we're good to go.
    Thanks

  18. #18
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula to count/sum number of cells needed to match value

    Swap G2 and H4, you've got them interchanged

    =MATCH($H$4,MMULT($AL23:INDEX($C$23:$AL$23,MATCH($G$2,$C$22:$AL$22,0)),--(COLUMN($AL23:INDEX($C$23:$AL$23,MATCH($G$2,$C$22:$AL$22,0)))>=TRANSPOSE(COLUMN($AL23:INDEX($C$23:$AL$23,MATCH($G$2,$C$22:$AL$22,0)))))),1)

  19. #19
    Registered User
    Join Date
    12-10-2019
    Location
    Whistler, Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Formula to count/sum number of cells needed to match value

    Quote Originally Posted by Ace_XL View Post
    Swap G2 and H4, you've got them interchanged

    =MATCH($H$4,MMULT($AL23:INDEX($C$23:$AL$23,MATCH($G$2,$C$22:$AL$22,0)),--(COLUMN($AL23:INDEX($C$23:$AL$23,MATCH($G$2,$C$22:$AL$22,0)))>=TRANSPOSE(COLUMN($AL23:INDEX($C$23:$AL$23,MATCH($G$2,$C$22:$AL$22,0)))))),1)
    AHA!

    Thank you so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula needed to count the number of days based on a date in other cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-06-2019, 11:49 AM
  2. [SOLVED] Simple formula needed to count cells based on item names in other cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-25-2019, 05:49 PM
  3. [SOLVED] Count number of cells from one match to another match on a different sheet
    By snowdude2004 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-12-2017, 05:23 PM
  4. [SOLVED] A formula needed to match a number and use a word
    By gary66 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-13-2013, 11:13 PM
  5. [SOLVED] Formula needed to only count total cells entered and not count adjacent text entered cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-07-2013, 06:22 PM
  6. Replies: 1
    Last Post: 12-22-2011, 07:24 PM
  7. Count the number of cells needed to sum a given value
    By fabvas in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2010, 09:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1