Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieve data based on multiple inputs (2007)

    I’m looking for a way either using Sumifs, strictly vlookup, or some way to retrieve data based on multiple selections.

    I’ve attached a sample spreadsheet as an .xls file, but am using 2007 which can utilize the Sumifs formula.

    The example shows a selection of John in July with a result of 70. I’d like it to automatically total this for me based on the selections I’ve made.

    And if there is a blank selection, I’d like it to total based on the single selection. For example, choosing “Jan” only would result in “100”.

    I’ve tried various forms of vlookups, indexing, and even sumifs. I keep running into a dead end.

    Thanks in advance for the help!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve data based on multiple inputs (2007)

    For example:

    =INDEX(B2:E13,MATCH(I2,A2:A13,0),MATCH(H2,B1:E1,0) )

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve data based on multiple inputs (2007)

    Or this:

    =VLOOKUP(I2,A2:E13,MATCH(H2,B1:E1,0)+1,FALSE)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve data based on multiple inputs (2007)

    And another one:

    =SUMPRODUCT(B2:E13*(A2:A13=I2)*(B1:E1=H2))

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve data based on multiple inputs (2007)

    Thank you for the quick reply.

    Both of those work if there is a selection in both cells, but if there is only one, I get the #N/A error.

    Is it possible to retrieve the total if there is only one selection?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve data based on multiple inputs (2007)

    You could use this array formula (confirm with Ctrl+Shift+Enter):

    =SUMPRODUCT(B2:E13*IF(ISBLANK(I2),1,A2:A13=I2)*IF( ISBLANK(H2),1,B1:E1=H2))

  7. #7
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve data based on multiple inputs (2007)

    Hey thanks. That works.

    I found out when transfering it to my real spreadsheet that because I have some #N/A results in my table, the formulas and array won't work. At least that's what I suspect. And that's probably why I kept getting bad results in the first place.

    I'll keep messing with this and if nothing else I can have it pull from some duplicate data that does not have any #N/A results.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •