none
v-lookup or alternative function to calculate individual allowance RRS feed

  • Question

  • hi there,

    is there a formula I can use either utilising v lookup or some other function that calculates the remaining balance in an individuals allowance.

    for example

    column a = Name

    column b = date of claim

    column c = description

    column d = amount

    is there a way other than having multiple tabs separated for each individuals name and claim details, to data enter all information on one tab and have the vlookup pick up the name and that $40 Has to be deducted from their annual $3600 allowance? and then summarise how much money is left remaining in that balance?

    Friday, January 27, 2017 7:39 AM

Answers

  • Y means that nothing Is deducted of the total, and N is to deduct of the amount?

    Formula in cell K4 of the example as follows. (Note: It is SUMIFS (Plural with s) not SUMIF (singular)

    =$K$2-SUMIFS($F$4:$F$20,$A$4:$A$20,J4,$E$4:$E$20,"N")

    Similar formula in cell G4 of the example as follows.

    =$G$2-SUMIFS($F$4:$F$20,$A$4:$A$20,A4,$E$4:$E$20,"N")


    Regards, OssieMac

    • Proposed as answer by Chenchen LiModerator Friday, February 3, 2017 7:28 AM
    • Marked as answer by MAY.VDW Friday, February 3, 2017 8:40 AM
    Thursday, February 2, 2017 10:21 AM

All replies

  • Bit hard to be precise without an example of actual data but the SUMIF AND SUMIFS can sum data based on criteria in other columns. See Help for more information on these functions.

    In the screen capture below, the following formula in cell D19 sums column D for Column A = "Bill" an Column B is in the date range 11 Jan 2017 to 19 Jan 2017. Edit the formula with your regional date format if not d/m/y format.

    =SUMIFS($D$2:$D$17,$A$2:$A$17,"=Bill",$B$2:$B$17,">=" & DATEVALUE("11 Jan 2017"),$B$2:$B$17,"<="& DATEVALUE("19 Jan 2017"))


    Regards, OssieMac

    Friday, January 27, 2017 10:44 AM
  • included sample.

    essentially each person has a yearly allowance of $3600. I want to subtract the amount of the yearly allowance to reflect remaining balance, but the formula needs to pick up the name. I don't want to separate each individual on different tabs if possible

    Monday, January 30, 2017 12:21 AM
  • I can't read the screen capture. Can you please upload an example workbook. If the workbook contains sensitive data then replace with dummy data.

    Guidelines to upload a workbook to OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click Copy the link and Paste into your reply on this forum.

    Regards, OssieMac

    Monday, January 30, 2017 3:43 AM
  • Hi,

    You could use formula =SUMIFS($F$4:$F$20,$A$4:$A$20,A4) to sum the account under same name. Criteria could be used as string "=STUART" and cellreference. For more information about, please visit SUMIFS function .

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 30, 2017 9:09 AM
    Moderator
  • It makes it a lot easier when I can see the real thing instead of guessing. Also it is better to return the workbook to provide you with a working example.

    See the file I have uploaded. In your original table you will see that the numbers are repeated for each time the name is repeated. The table to the right contains a unique list of the names so that the remaining amounts only appear once.

    It is best to have a cell containing the allocated amount rather than hard enter the amount in the formulas and then you only need to change that cell if the allocated amount changes. I have used cells G2 and L2.See my comments in red on the worksheet as to how I have used Custom NumberFormat so that these amounts can be displayed with accompanying text. The formulas reference these cells and subtract the spent amounts from them.

    See the formulas in the cells with the results. Note the absolute referencing (with $ signs) for the $G$2 or $L$2 and also for the ranges but relative referencing (no $ signs)  for the cell with the name that is to be found.

    https://1drv.ms/u/s!ArAXPS2RpafCg1jCRLHvTYrSieEw

    To create a unique list as I have done:

    1. Select the list of names (including column header) and copy
    2. Paste to a clear area on the worksheet (Only need to select first cell of paste)
    3. Select the pasted names (including column header)
    4. Select Data ribbon
    5. Select Remove duplicates (In Data tools block towards middle of ribbon)
    6. Check the box "My data has column headers" and the column should select by default but if not select it and then click OK.

    Regards, OssieMac

    Monday, January 30, 2017 9:11 AM
  • thank you, this is great,

    how would you then work in the operational need component of the example, i.e Y means that nothing Is deducted of the total, and N is to deduct of the amount?

    Thursday, February 2, 2017 6:28 AM
  • Y means that nothing Is deducted of the total, and N is to deduct of the amount?

    Formula in cell K4 of the example as follows. (Note: It is SUMIFS (Plural with s) not SUMIF (singular)

    =$K$2-SUMIFS($F$4:$F$20,$A$4:$A$20,J4,$E$4:$E$20,"N")

    Similar formula in cell G4 of the example as follows.

    =$G$2-SUMIFS($F$4:$F$20,$A$4:$A$20,A4,$E$4:$E$20,"N")


    Regards, OssieMac

    • Proposed as answer by Chenchen LiModerator Friday, February 3, 2017 7:28 AM
    • Marked as answer by MAY.VDW Friday, February 3, 2017 8:40 AM
    Thursday, February 2, 2017 10:21 AM
  • thank you !! :)
    Friday, February 3, 2017 12:46 AM
  • Hi,

    If your issue has been resolved, I suggest you mark helpful post as answer to close the thread.

    Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 3, 2017 7:28 AM
    Moderator