+ Reply to Thread
Results 1 to 7 of 7

How to Use VLookup inside SUMIFS ?

  1. #1
    Registered User
    Join Date
    08-02-2012
    Location
    Pakistan
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    43

    Question How to Use VLookup inside SUMIFS ?

    I'm trying to calculate summary from data it's working fine with SUMIFS but it will take me longs to edit each Range in a single Row. A example of summary including data and preconfig SUMIFS attached and everything is working fine but I have bunch of products to list and it will take long, I think there is a possibility to use VLOOKUP with SUMIFS.

    Can't I just use to define name for F:S on items sold sheet and in summary sheet i can have results with company name & datawise?

    Looking for a solution.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to Use VLookup inside SUMIFS ?

    Instead of this in D5 of the Summary sheet:

    =SUMIFS(CPU, Coy,D$4, Date,">="&$E$2, Date, "<="&$H$2)

    you can change it to this:

    =SUMIFS(INDEX('Items Sold'!$F$16:$S$1048576,0,MATCH($B5,'Items Sold'!$F$14:$S$14,0)), Coy,D$4, Date,">="&$E$2, Date, "<="&$H$2)

    and then you can copy this across and down as required.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-02-2012
    Location
    Pakistan
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    43

    Re: How to Use VLookup inside SUMIFS ?

    Thank you very much, that's what i was asking.
    I understand your defined formula, but this Zero before MATCH & before COY? what is it for and it's role? Can you tell me that?

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to Use VLookup inside SUMIFS ?

    The second one forces the MATCH function to look for an exact match. The first one basically means the data can be on any row.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    08-02-2012
    Location
    Pakistan
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    43

    Re: How to Use VLookup inside SUMIFS ?

    Thank you Pete. I have applied formula and it's working fine.
    One more things come up. I'm trying to do double MATCH but it giving me error.
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to Use VLookup inside SUMIFS ?

    That looks like you are trying to get data from another workbook (which will need to be open in the same instance of Excel, otherwise you will need to include the full path before the filename). I'm not sure why you think you need to use two MATCH functions, as the INDEX function will result in just a single cell rather than a range.

    I'm not sure if SUMIFS will allow you to do this, but it is obviously a different problem than the one we tackled earlier, so it might be better to start a new thread on this, and I would suggest that you attach both workbooks to that thread to help set the scene more clearly.

    Pete

  7. #7
    Registered User
    Join Date
    08-02-2012
    Location
    Pakistan
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    43

    Re: How to Use VLookup inside SUMIFS ?

    Ok, Right now i'm messed up. I'll start new thread with attachment tomorrow. Thanks for your time mate really appreciate it.

+ 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] IF formula inside SUMIFS?
    By ovehendrix in forum Excel General
    Replies: 9
    Last Post: 10-18-2023, 04:55 AM
  2. [SOLVED] Nest SumIFS inside subtotal
    By billrogers184 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-16-2017, 04:02 PM
  3. Problem using Lookup inside Sumifs
    By ap1980 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-06-2017, 05:22 AM
  4. Help nesting an OR function inside a SUMIFS function
    By wes228 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-21-2014, 06:22 PM
  5. Replies: 1
    Last Post: 10-02-2012, 04:27 PM
  6. Excel 2007 : SUM inside SUMIFS?
    By thart21 in forum Excel General
    Replies: 4
    Last Post: 09-30-2011, 01:58 PM
  7. Excel 2007 : sumifs inside of a vlookup ?
    By budchevy in forum Excel General
    Replies: 4
    Last Post: 05-15-2011, 06:33 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