array formula question

Posted by rainy on July 17, 2001 8:39 AM

i want to sum columns with the following condition:
=SUM(IF((B2:B12=2)*(D2:D12=2000),E2:E12))

only the range of data that i need to use changes dynamically.
the user selects the year he wants to view, and i imort the data from a file.
each year will appear in a diffrent location, and i dont know how many rows of data it will have.
how can i change the formula so it will work for that?

Posted by Barrie Davidson on July 17, 2001 9:15 AM

I would recommend using named ranges instead of cell addresses. For example, name range B2:B12 to Range1 and change your formula to:
=SUM(IF((Range1=2)*(D2:D12=2000),E2:E12))

This way, all you need to do is to name the imported data range (I assume you are doing the import via a macro). You can do the same for E2:E12.

Hope this helps,
Barrie