+ Reply to Thread
Results 1 to 6 of 6

Using Vlookup with sumif on different sheets of the same workbook

  1. #1
    Registered User
    Join Date
    05-04-2017
    Location
    Bartlesville, OK
    MS-Off Ver
    2010
    Posts
    9

    Using Vlookup with sumif on different sheets of the same workbook

    Hello,

    I am trying to use either Vlookup with sumif, OR match & Index with sumif in a spreadsheet with multiple sheets, but not having any luck.

    I am in a cell titled "SUN" cell F3(sheet +5 Summary) and trying to lookup all data from B3 (2000) on sheet "Attendance" where 2000 (and several other data points) are in column "E" and when "2000" is found in column "E" check column "O" and add all matches of "2000" from E to all data >= .09 in column "O".

    So if 2000 appears 20 times in "E" (Attendance sheet), it will have 20 corresponding data points in column "O", but I only want to sum those data points from "O" if they are >=.09.

    Additionally, my preference right now is to NOT use VBA if it's possible without it.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Using Vlookup with sumif on different sheets of the same workbook

    Attach a sample workbook.

    Make sure there is just enough data to demonstrate your need.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    05-04-2017
    Location
    Bartlesville, OK
    MS-Off Ver
    2010
    Posts
    9

    Re: Using Vlookup with sumif on different sheets of the same workbook

    I have added fictitious data to the +5Summary sheet in F2 and F3.

    F2 should look up all 2000 in Attendance sheet, column E (E3 and E6 in this example) and count the number of times it occurs (2) and sum "O" if it is >+.09 (.10 + .10 = .20)

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,684

    Re: Using Vlookup with sumif on different sheets of the same workbook

    in F3

    =COUNTIF(Attendance!E:E,2000)

    in F4

    =SUMIFS(Attendance!O:O,Attendance!E:E,2000,Attendance!O:O,">0.09")

  5. #5
    Registered User
    Join Date
    05-04-2017
    Location
    Bartlesville, OK
    MS-Off Ver
    2010
    Posts
    9

    Re: Using Vlookup with sumif on different sheets of the same workbook

    John,

    Thank you, the F4 formula worked perfectly.

    I think perhaps I wasn't clear enough in my initial thread, as in the sumif, I only want to count the occurrences of 2000 "E" where the the data in "O" is >=.09 In this instance it would only be 1 occurrence where that happens.

    Thanks again!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,684

    Re: Using Vlookup with sumif on different sheets of the same workbook

    Use COUNTIFS and add the second criteria as per SUMIFS

+ 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. Vlookup between two sheets with in a workbook via VBA
    By Andy308 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-18-2016, 09:59 AM
  2. [SOLVED] Vlookup from one workbook to another workbook with multipule sheets
    By sally.simpkin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-30-2013, 06:22 AM
  3. Replies: 0
    Last Post: 08-17-2012, 02:02 PM
  4. Vlookup several sheets in another workbook
    By nisha1110 in forum Excel General
    Replies: 11
    Last Post: 06-24-2009, 06:17 AM
  5. SumIf or Vlookup on multiple sheets at once
    By Dubbelito in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-29-2008, 06:41 AM
  6. Vlookup for two sheets in same workbook
    By Roberta in forum Excel General
    Replies: 4
    Last Post: 02-23-2005, 09:06 AM
  7. [SOLVED] VLOOKUP across all sheets in a workbook
    By Pawel P. in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-12-2005, 09:06 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