sbozicevic
New Member
- Joined
- Mar 13, 2007
- Messages
- 13
Hi there,
I'm running a complex if/and on a number of cells on top of a large (150k) data set, and I'd like to optimize it in Excel 2007. It takes a long time to refresh data, and I'm wondering if by some optimization my workbook will actually be usable.
This is what I'm currently using:
=SUM(IF($A4='Raw Data'!$A$2:$A$150000, IF(B$2='Raw Data'!$N$2:$N$150000,1,0),0))
I am trying to accomplish something like:
=SUM(IF(AND($A4='Raw Data'!A2:A150000, B2='Raw Data'!N2:N150000),1,0)))
But I can't get the AND to work in an array; it seems as if it isn't processing all the values in the array independantly. I want to add a value (1) to the sum if the value of A4 matches the data in column A and the value in B2 matches the data in column N.
Any ideas?
I'm running a complex if/and on a number of cells on top of a large (150k) data set, and I'd like to optimize it in Excel 2007. It takes a long time to refresh data, and I'm wondering if by some optimization my workbook will actually be usable.
This is what I'm currently using:
=SUM(IF($A4='Raw Data'!$A$2:$A$150000, IF(B$2='Raw Data'!$N$2:$N$150000,1,0),0))
I am trying to accomplish something like:
=SUM(IF(AND($A4='Raw Data'!A2:A150000, B2='Raw Data'!N2:N150000),1,0)))
But I can't get the AND to work in an array; it seems as if it isn't processing all the values in the array independantly. I want to add a value (1) to the sum if the value of A4 matches the data in column A and the value in B2 matches the data in column N.
Any ideas?