Excel 2007 : SUMIFS with criteria/sum ranges in a cell drop-down

[CST]

I've been scouring every Excel forum I can find for what I believe is a relatively simple problem, but still can't find the solution...

Here's the outline:

I want to use two dropdown cells as references for the criteria in the SUMIFS function. For the criteria, this is simple since it is just numeric. But for the criteria range and sum range, I am trying to reference a cell containing a data-validated drop-down list of the named ranges in the sheet. So far, I have gotten to this functioning formula:

=SUMIFS(INDEX(Salad,,2),INDEX(Salad,,1),">="&B4)

where "Salad" is one named range. What I want to be able to do is refer to a cell that contains a drop-down list with all the other named ranges (Salad1, Salad2, Salad3) in it but Excel keeps giving me a #REF! error.

Thanks!

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[CST]

NBVC,

Uploaded an example dummy file in original post. Hopefully, it explains the problem more clearly.

[NBVC]

Try:

=SUMIFS(INDEX(INDIRECT($A$5),,COLUMNS($A$1:B$1)),INDEX(INDIRECT($A$5),,1),">="&$B$5)

copied across the 4 columns...

Note: To avoid blanks in the drop down, create a separate list somewhere in consecutive cells and link Data Validation to that list.

[CST]

Awesome! Thanks so much.

Now I need to figure out why that works.