If / AND statement with Arrays

sbozicevic

New Member
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?

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Jonmo1

MrExcel MVP
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

MrExcel MVP
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

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

sbozicevic

New Member
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!

Replies
7
Views
1K
Replies
3
Views
213
Replies
1
Views
473
Replies
0
Views
452
Replies
9
Views
1K

1,191,167
Messages
5,985,053
Members
439,936
Latest member
BSR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back