Share via

SUMIFS Formula and using VLOOKUP as criteria

Anonymous
2021-08-05T19:24:10+00:00

Hello,

I am working on a SUMIFS formula and am trying to make a criteria range flexible by using Vlookup.

=SUMIFS(Details!$L:$L,Details!$H:$H,$A$10,VLOOKUP($D$2,RefData$E$32:$G$77,3,FALSE),$D$2)

Based on the vlookup, the criteria range would be one of 3 options:

Details!$A:$A or Details!$B:$B or Details!$C:$C

the SUMIFS formula appears not to like that I am trying to use a Vlookup to select a criteria range. can I make it work?

the alternate approach is to make a long conditional IF formula to cover 3 conditions for Columns A, B or C on my RefData tab, but would prefer to avoid that.

Thanks!

Glenn Halpern

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2021-08-05T23:23:47+00:00

    Hi,

    The fourth input of the SUMIFS() function should be a range. A VLOOKUP() function returns a scalar value (not a range). Share some data, describe the question and show the expected result.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2021-08-05T20:00:41+00:00

    Hi

    My name is André. I am an independent consultant.

    >I believe the best way to actually do this is to implement the double IF.

    =IF(Vlookup=A,A,IF(Vlookup=B,B,C))

    That way you have absolute control over each condition.

    Answer here so I can continue helping you.

    André.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2021-08-06T05:18:17+00:00

    Hi . I’m RN, let's see and check your queries.

    I'm sorry to hear about this issue.

    May you share a sample excel file on Onedrive so that we can analyze the requirements and data you have?

    Kindly also indicate the expected result.

    0 comments
  4. Anonymous
    2021-08-06T02:25:14+00:00

    Unfortunately I ran out of ideas to solve this, I hope someone can help you.

    0 comments
  5. Anonymous
    2021-08-05T21:02:22+00:00

    Hi Andre, thanks for reaching out and trying to help out.

    I thin I may have been unable to explain my issue clearly.

    I played with the functions and learned that I can use INDIRECT function to pick up the text from my vlookup as a Reference in order to use as the criteria range for my SUMIFS formula, which ended up looking like this with new criteria range in green -

    =SUMIFS(Details!$L:$L,Details!$J:$J,$A8,Details!$B:$B,I$6,INDIRECT(VLOOKUP($D$2,'Reporting Levels'!$C$72:$E$114,3,FALSE)),'Entities View'!$D$2)

    thanks again!

    glenn

    0 comments