+ Reply to Thread
Results 1 to 9 of 9

ERROR: Nested VLOOKUP inside a SUMIFS function

  1. #1
    Registered User
    Join Date
    04-14-2021
    Location
    Long Island, NY
    MS-Off Ver
    Office 365
    Posts
    37

    ERROR: Nested VLOOKUP inside a SUMIFS function

    Hi all!

    I'm having difficulty nesting a VLOOKUP function inside a SUMIFS function. Excel keeps telling me, "Not trying to type a formula?" However, when I used the same VLOOKUP formula nested inside a SUMIF (not plural) function, it works just fine.

    Where might I be going wrong?

    Thanks!

    =SUMIFS('Current Data'!$P:$P,VLOOKUP(Summary!$C2,'Market Mapping'!$A$3:$B$122,2,FALSE),'Current Data'!$K:$K,'Current Data'!$A:$A,Summary!F$1,'Current Data'!$D:$D,Summary!$B2,'Current Data'!$I:$I,"n")

    FormulaIssue.PNG

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: ERROR: Nested VLOOKUP inside a SUMIFS function

    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    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
    28,005

    Re: ERROR: Nested VLOOKUP inside a SUMIFS function

    I think you have Criteria Range and Criteria reversed

    =SUMIFS('Current Data'!$P:$P,'Current Data'!$K:$K,VLOOKUP(Summary!$C2,'Market Mapping'!$A$3:$B$122,2,FALSE),'Current Data'!$A:$A,Summary!F$1,'Current Data'!$D:$D,Summary!$B2,'Current Data'!$I:$I,"n")

  4. #4
    Registered User
    Join Date
    04-14-2021
    Location
    Long Island, NY
    MS-Off Ver
    Office 365
    Posts
    37

    Re: ERROR: Nested VLOOKUP inside a SUMIFS function

    Hi John,

    Thanks for the response!

    Trouble that I'm experiencing is that my lookup value is 'Illinois' on my report. However, on the data tab, there is not explicitly a 'State' field, but instead a 'State-City' field (e.g., IL-Chicago, IL-Peoria, etc.). I've created a map (a separate tab with a table that shows which state each 'State-City' field belongs to.

    In the formula:
    Column P of the 'Current Data' tab is Sales.
    Column K of the 'Current Data' tab is 'State-City'.
    Column C of the 'Summary' tab is 'State' (lookup value in the report where the formula is)
    Range A3:B122 of the 'Market Mapping' tab is 'State-City' and 'State'
    Column A of the 'Current Data' tab is 'Date' by month (e.g., 01/01/20, 02,01/20)
    Row 1 Column F of the 'Summary' tab is the horizontal dates of the report (01/01/20 - 01/01/21)
    Column D of the 'Current Data' tab is the segment (e.g., Small, Transition)
    Column B of the 'Summary' tab is the segment lookup value
    Column I of the 'Current Data' tab is a Y/N indicator

  5. #5
    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
    28,005

    Re: ERROR: Nested VLOOKUP inside a SUMIFS function

    Please read the yellow banner at the top of this page on how to attach a file.

  6. #6
    Registered User
    Join Date
    04-14-2021
    Location
    Long Island, NY
    MS-Off Ver
    Office 365
    Posts
    37

    Re: ERROR: Nested VLOOKUP inside a SUMIFS function

    Hi John,

    Please see the attached document.

    Thank you!
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: ERROR: Nested VLOOKUP inside a SUMIFS function

    Report


    E2=SUMPRODUCT(SUMIFS(Data!$G$2:$G$19,Data!$E$2:$E$19,INDEX('Market Map'!$A$2:$E$4,,MATCH(Report!$C2,'Market Map'!$A$1:$E$1,0)),Data!$B$2:$B$19,Report!$B2,Data!$A$2:$A$19,Report!E$1,Data!$C$2:$C$19,"N"))

    Copy across and down




    I changed the app market sheet to use

    the formula
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-14-2021
    Location
    Long Island, NY
    MS-Off Ver
    Office 365
    Posts
    37

    Re: ERROR: Nested VLOOKUP inside a SUMIFS function

    Caracalla! You are the governor! Thank you for sharing your expertise!

    I understand and am familiar with everything you've done. However, can you educate me why the SUMPRODUCT function is used as the parent function? Is it to simply classify the nested formulas as arrays?

  9. #9
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: ERROR: Nested VLOOKUP inside a SUMIFS function

    Tutorial

    https://www.youtube.com/watch?v=s_St...nel=ExcelIsFun




    In the channel you will find tutorials for each topic related to the functions you want



    Sorry for my English
    Last edited by CARACALLA; 04-16-2021 at 10:36 AM.

+ 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] How to add other formula inside the SUMIFS Function
    By Bluya in forum Excel General
    Replies: 3
    Last Post: 01-31-2020, 10:22 AM
  2. [SOLVED] Using TODAY function inside the SUMIFS function
    By taraberg_321 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-26-2019, 10:32 AM
  3. [SOLVED] How to Use VLookup inside SUMIFS ?
    By admirable in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-13-2018, 03:55 PM
  4. RAND function nested inside a VLOOKUP
    By SuperWhistle in forum Excel General
    Replies: 2
    Last Post: 11-04-2016, 12:58 AM
  5. [SOLVED] RIGHT Function nested inside of IF
    By jakeisbill in forum Excel General
    Replies: 6
    Last Post: 10-16-2012, 12:01 PM
  6. Excel 2007 : sumifs inside of a vlookup ?
    By budchevy in forum Excel General
    Replies: 4
    Last Post: 05-15-2011, 06:33 PM
  7. Worksheet_Change - Error with nested If-then inside Case
    By m1notaur in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-24-2010, 10:32 AM

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