Retrieve data based on multiple inputs (2007)

Topic

New Reply

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!

Reply | Quote

Replies

For example:

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

Reply | Quote

Or this:

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

Reply | Quote

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?

Reply | Quote

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))

Reply | Quote

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.

Reply | Quote

And another one:

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

Reply | Quote