Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

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?

Re: array formula question

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.