If / AND statement with Arrays

[sbozicevic]

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?

 

[Jonmo1]

You've got it reversed...

You have
if Value = Array

It should be
If Array = Value

Try
=SUM(IF('Raw Data'!$A$2:$A$150000=$A4, IF('Raw Data'!$N$2:$N$150000=B$2,1,0),0))

Also, don't forget to enter with CTRL + SHIFT + ENTER

Hope this helps...

 

[Domenic]

The AND function only returns a single value, so you'll have use a different approach, such as the first formula you've posted. As far as efficiency is concerned, concatenate Column A and Column N into a new column, then use COUNTIF on this new column.

Hope this helps!

 

[RoryA]

A pivot table might be a better option if you are summarising a lot of data like this.

 

[sbozicevic]

Thanks for your input everyone. I've changed around the way I coded it to be a little less CPU intensive.

I concatenated the two columns as suggested and that helps some.

Thanks!