+ Reply to Thread
Results 1 to 5 of 5

Sum of 2 criteria's in different sheet

  1. #1
    Registered User
    Join Date
    09-30-2020
    Location
    DC
    MS-Off Ver
    2016
    Posts
    3

    Sum of 2 criteria's in different sheet

    I have been receiving Value errors on this so I hope I can find some type of solution on here. I tried the index/match route but I don't think it works when you have multiple rows that you are trying to add up.

    1st: In sheet 2 I am trying to find all of A2 on that sheet with sheet1 and get the sum that have a "YES" on column L. If Column L says "NO" it needs to be omitted. (Which will be "SUM OF ORDER")
    2nd: for SUM OF BUDGET (Column C in sheet 2) I am trying to add up all of the ones that have a "YES" on Column L as well

    I hope this makes sense! I have attached the workbook.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,582

    Re: Sum of 2 criteria's in different sheet

    Hi and welcome to the forum
    B2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    C2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You may also like to investigate using a Pivot Table and avoid the need for formulae altogether
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-30-2020
    Location
    DC
    MS-Off Ver
    2016
    Posts
    3

    Re: Sum of 2 criteria's in different sheet

    Hi,

    Thank you so much for your prompt response!

    This unfortunately did not work, this only had the criteria for if it is YES add it up. I've tried the SUMIFS function and unfortunately it still wasn't giving me what I am looking for.

    Also I'm not sure if I explained it properly but your answer still didn't add up properly. It gave me $111 for B2 when it should have been $5.04M

    I essentially want to know what the sum of Sheet1 D2:K34 is if Sheet 1 L2:L34 says "YES" and Sheet 2 A2 and Sheet1 A2:34 match.

    So if it has order#10 and is a YES add all of January-august that it applies to


    i.e: Column C would be =SUMIFS(Sheet1!$C2:$C$34,Sheet1!$L$2:$L$34,"YES",Sheet1!A2:A34,Sheet2!A3)

    But I keep getting value error on column B

    _____

    The only thing that I can see working is =SUMIFS(Sheet1!$D:$D,Sheet1!$L:$L,"YES",Sheet1!$A:$A,Sheet2!A2) + SUMIFS(Sheet1!$E:$E,Sheet1!$L:$L,"YES",Sheet1!$A:$A,Sheet2!A2).....

    but there has got to be a simpler way to do this?
    Last edited by hsouiri; 09-30-2020 at 07:47 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    518

    Re: Sum of 2 criteria's in different sheet

    worksheet name : sheet2 , B2 cell formula
    HTML Code: 
    =SUM(SUMIFS(OFFSET(Sheet1!D:D,,{0,1,2,3,4,5,6,7}),Sheet1!A:A,A2,Sheet1!L:L,"YES"))
    C2 cell formula
    HTML Code: 
    =SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!L:L,"YES")
    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu
    Last edited by wk9128; 09-30-2020 at 10:39 PM.

  5. #5
    Registered User
    Join Date
    09-30-2020
    Location
    DC
    MS-Off Ver
    2016
    Posts
    3

    Re: Sum of 2 criteria's in different sheet

    Quote Originally Posted by wk9128 View Post
    worksheet name : sheet2 , B2 cell formula
    HTML Code: 
    =SUM(SUMIFS(OFFSET(Sheet1!D:D,,{0,1,2,3,4,5,6,7}),Sheet1!A:A,A2,Sheet1!L:L,"YES"))
    C2 cell formula
    HTML Code: 
    =SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!L:L,"YES")
    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu
    Genius! 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. Replies: 8
    Last Post: 05-08-2016, 12:48 AM
  2. Replies: 0
    Last Post: 05-02-2016, 07:59 AM
  3. Replies: 2
    Last Post: 01-23-2016, 01:16 PM
  4. Criteria from Sheet 1 copied over to Sheet 2 if it meets certain criteria
    By rangeroverdude in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2015, 06:00 PM
  5. Replies: 1
    Last Post: 09-03-2013, 07:41 PM
  6. Replies: 4
    Last Post: 01-08-2013, 12:37 PM
  7. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM

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