+ Reply to Thread
Results 1 to 5 of 5

Sum Amount based on Multiple Criteria

  1. #1
    Registered User
    Join Date
    01-08-2020
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    67

    Sum Amount based on Multiple Criteria

    Hi,

    I am looking to allocate a cost based on multiple criteria.

    In the attached example I have $1m and want to allocate this to all ID's under the project number. However I want to only allocate to ID's which have a status of "Active" or "Still Active".

    I am also looking at ways of splitting the $1m evenly to these ID's if possible.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,058

    Re: Sum Amount based on Multiple Criteria

    I changed the lay out of the file.


    K2=IF(ISERROR(MATCH($I4,$N$2:$N$8,0)),"",G4&" yes") and drag down

    L2=IF(K4="","",VLOOKUP($G4,$B$4:$C$15,2,0)/COUNTIF($K$4:$K$8,K4)) and drag down

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    01-08-2020
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    67

    Re: Sum Amount based on Multiple Criteria

    Thank you very much for your help.

    I did have one other question if you wouldn't mind helping.

    I was wondering what formula I would need to count all ID's with "Active" and "still active" status.

    I am currently trying - =COUNTIFS(I:I,OR("Active","Still Active"))

    But this is not working. Not sure what I am missing.

    Thanks again

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,058

    Re: Sum Amount based on Multiple Criteria

    M1=Countif($M$4:$M$9,"yes")

    See the attached file.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,058

    Re: Sum Amount based on Multiple Criteria

    Thanks for the rep.

    Glad I could help.

    Thanks for marking the question solved.

+ 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] Allocating amount across several lines based on 2 criteria
    By Fish55 in forum Excel General
    Replies: 4
    Last Post: 06-23-2022, 12:13 PM
  2. [SOLVED] Need to Bring in MAX amount based on multiple criteria.
    By SterlingSilver67 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-03-2022, 02:58 PM
  3. [SOLVED] Calculation with multiple criteria- CAP Amount, Min. Amount etc.
    By Shareez Saleem in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-15-2021, 07:59 AM
  4. [SOLVED] Top 3 products based on multiple criteria (Name and Amount)
    By blak9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-13-2020, 07:42 AM
  5. Sumifs budgeted amount based on 2 criteria
    By Suzanne1307 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2018, 03:49 PM
  6. [SOLVED] Return total amount values based on multiple criteria
    By MyStix01 in forum Excel General
    Replies: 3
    Last Post: 10-12-2017, 09:05 AM
  7. sumif-add amount to another cell based on two criteria
    By gabrielinlompoc in forum Excel General
    Replies: 0
    Last Post: 02-09-2006, 08:25 PM

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