Array Formula to Non Array

[ExceLLemon]

Hello all,

I currently uses a ton of Array formulas(sample below) to count a database, does Array formulas slow down EXCEL? because my excel runs really slow, was wondering if anyone can construct a NON Array formula base on this? I want to change all my array formulas to Non. THANKS!!!

=SUM(IF(SSP=F170,IF(SP=G170,IF(Day=A$1:B$1,IF(Single=D170,1,0),0))))

[hemesh]

could you post a sample book. Try using sumifs

[Tony Valko]

They're not doing a sum. They're doing a count.

[ExceLLemon]

Hi, thanks for the advice, the work book is too big and probably not a good idea to upload work stuff. I would have to change so much stuff in order to upload, but basically, the SSP, DAY. etc are name range, I have one sheet that consist all the data, each record is in a row, I basically just want the formula to look up and count the number of times if all criteria are met...in a NON Array formulas

[Tony Valko]

In array formulas you should avoid using entire columns as range references.

Can you post the formula replacing the named ranges with their actual range addresses?

I'm curious about this portion:

IF(Day=A$1:B$1

Is Day a 1 dimensional array?

[ExceLLemon]

It would be something like this....Day=D2:D2997, SSP=G2:G2997, Single=D2:D2997 and so forth.
I have one sheet that is the "Database" and then one sheet where I put the formulas...
For example when I put "thur" "yes" "15" then, the formulas returns the number of records that has those criteria. It is actually counting base on the criteria. I am sorry, I thought I am good in Excel , I don't even know what a dimensional array is.... =(

[Tony Valko]

OK, I'm still wondering about this portion of the formula:

IF(Day=A$1:B$1

Since Day refers to D2:D2997, what's in A1:B1?

Can you post about 20 rows worth of data and tell us what result you expect?

Array dimensions are...

A single row or column of data would be 1 dimensional:

A1:A10 = 1 dimensional
A1:J1 = 1 dimensional

Multiple rows/columns would be 2 dimensional:

A1:C10 = 2 dimensional
A1:J2 = 2 dimensional

And, we can even have a 3 dimensional array:

Sheet1 thru Sheet5 A1:C10 = 3 dimensional

[ExceLLemon]

BA CA <<Day Codes

Database
Week Date Lbs Single Time SSP SP Origin Type O/U Date Delivery Delivery Diff Overage Day
W02 09/17/06 5 No 1.00 100 47 Orange200 Over 09/17/06 Apple 250 50 350.0 CA
W02 09/17/06 25 No 1.00 100 47 Apple 150 Over 09/17/06 Orange 178 28 128.0 BA
W02 09/17/06 25 No 1.00 300 38 Grapes100 Under 09/17/06 Blueberries 125 25 (75.0) AA
W02 09/17/06 100 No 1.00 200 38 Beef 100 Over 09/17/06 Kobe 125 25 25.0 DA


Analysis Sheet

Week Date Lbs Single Time SSP SP Origin Type O/U Date Delivery Delivery Diff Overage Day
W02 09/17/06 SUN No 1.00 100 47 Orange 09/17/06 Apple 0 (100.0)

SUM(IF(SSP=F170,IF(SP=G170,IF(Day=A$1:B$1,IF(Single=D170,1,0),0))))

The formula now go and look at the database sheet and see when I type SSP at 100 and type SP 47 and the if the CODE is BA or CA
It should count there is 2 instance, therefore returns a number 2
I am trying to Predict the CODE base on the criteria SSP & SP or other criterias…
My worksheet which someone gave me long time ago, I have been using for many years…my computer is not slow
but I believe these formulas are really slowing it down. And I don't like the shif, control enter…always forgets…

Thank you so much for looking at this!!!!

[Tony Valko]

I can't make out what data is what and where it's supposed to go.

Can you post a SMALL sample file with about 20 rows worth of data and let us know what result you expect. Include your current formula so we can see what result it returns.

[vandan_tanna]

Have you tried replacing your sum(if(...) with countifs?

That function wasn't available before excel 2007. But since you have excel 2007, you should use it.