+ Reply to Thread
Results 1 to 5 of 5

Sum with more than one column criteria

  1. #1
    Registered User
    Join Date
    12-20-2022
    Location
    Joplin, MO
    MS-Off Ver
    Office 365
    Posts
    2

    Sum with more than one column criteria

    I have some data that looks like this:

    A B C
    Project No. Root Project Compensation
    000016.000 000016 80705
    000016.001 000016 8000
    000016.002 000016 2295
    000016.003 000016 16440
    000017.000 000017 35000
    000017.001 000017 1500

    I want to sum the Compensation for all the sub-numbers in the Project No. column of the Root Project number and have them display on the same row as the .000 number. In other words, I want to sum the Compensation for Project Nos 000016.000 + 000016.001 + 000016.002 + 000016.003 and have it show up on the same row (unshown column D) as the .000 Project No. in column A, otherwise leave the field blank. This would result in a total of 107440 (sum of 000016.000 thru 000016.003) in the first row with all the other Project No. 000016 rows being blank. So basically, if Root Project = LEFT(Number column) AND Project No. has .000 at the end of it show me the Compensation total of all Project Nos that start with 000016 on the 000016.000 row in column D. Hope that makes sense.

    I have tried different variations of SUMIFS, SUMPRODUCT, and nested LEFT statements in SUMIFS with no luck.

    Thanks in advance for your assistance and expertise!
    Last edited by lthomashlb; 12-20-2022 at 03:14 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,513

    Re: Sum with more than one column criteria

    Try:
    =IF(RIGHT(A2,4)=".000",SUMPRODUCT(--(LEFT($A$2:$A$7,6)=B2)*$C$2:$C$7),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

  3. #3
    Registered User
    Join Date
    12-20-2022
    Location
    Joplin, MO
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Sum with more than one column criteria

    Lovely! Thanks so much!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,513

    Re: Sum with more than one column criteria

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Sum with more than one column criteria

    maybe
    with Power Query
    List Root Project Compensation
    000016.000, 000016.001, 000016.002, 000016.003
    16
    107440
    000017.000, 000017.001
    17
    36500
    Last edited by sandy666; 12-20-2022 at 04:27 PM.

+ 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] Usine sumifs to change criteria range column based on dynamic criteria
    By Luiscarlos in forum Excel General
    Replies: 5
    Last Post: 11-19-2020, 09:33 AM
  2. Replies: 9
    Last Post: 12-10-2018, 03:01 PM
  3. [SOLVED] Countifs multiple criteria- criteria if cell is greater than another i adjacent column
    By Sircool1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2018, 11:23 AM
  4. Replies: 4
    Last Post: 04-06-2017, 03:42 PM
  5. Replies: 2
    Last Post: 04-22-2016, 01:25 PM
  6. [SOLVED] Count of rows in an array that meet criteria in column 2 and different criteria in column3
    By reynoldslarry in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-09-2013, 01:51 AM
  7. Replies: 2
    Last Post: 10-05-2011, 12:43 PM

Tags for this Thread

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