Excel Array Formula problem 3

[waynerenaud]

Hi All,
Can someone please explain why, when using an array formula, if I try and reference an entire column it fails with a #NUM error, if I use a range it works ok:
Example:
this is entered on one line using ctrl+shift+enter & errors
=SUM(IF((All!B:B='LA Summary'!B5)*(All!$E$2:$E$65536="incomingphone&quot,1))
If I change the B:B to
=SUM(IF((All!$B$2:$B$65536='LA Summary'!B5)*(All!$E$2:$E$65536="incomingphone&quot,1))
It works ok ?????
Can you use an entire column in an array formula, if so does it need square brackets [] or any other wierd characters to make it work. I did try putting 'All'! for the sheet name, but excel strips it back to All! when entered. Thanks to anyone that can shed some light on this.

 

[PHV]

Have you headers in row 1 ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[waynerenaud]

Yes I do, column B has a header 'Date' and column E has header 'Call Type' I get the same problem with column E if I use E:E

 

[PHV]

So how do you want to sum a header ?

 

[waynerenaud]

??? My understanding of this array is that it applies a value of 1 if the criteria is true, therefore if 'LA Summary'!B5 has 01/02/2004 then it will equate to true if any date in colum B on sheet 'All'! matches, then it checks column E for "incomingphone" then multiplies the matching result giving the equivalent of a count. Surely if it starts in row 1 or row 2 should make no difference it will read the header and determine if it matches the required criteria.
Maybe I am missing the plot completely with this..........

I do appreciate your help PHV, stay with me on this, I need to learn

 

[waynerenaud]

After further tests (using simpler examples) the header row makes no difference. Example:
put this formula in cell A1 on sheet2
{=SUM(IF((Sheet1!A1:A9="Dave&quot*(Sheet1!B1:B9=10),1))}
it works great and returns an answer of 5
change the formula to this
{=SUM(IF((Sheet1!A:A="Dave&quot*(Sheet1!B:B=10),1))}
and you get #NUM error...........why? :-(
Note: I am using ctrl+shift+enter to apply array formula.
(Also tried putting formula on sheet1 in case it was the sheet name that was the problem, but still the same)

Sheet1
A B
1 Dave 10
2 Fred 10
3 Wayne 11
4 Dave 11
5 Wayne 10
6 Dave 10
7 Dave 10
8 Dave 10
9 Dave 10

 

[PHV]

I apologize for my erroneous post.

 

[Loomah]

Hi waynerenaud

I'm a little stumped by this so I'll be watching for a resolution!

My first thought was to use a non array formula but it made no difference.

BTW the option would be to use SUMPRODUCT
eg =SUMPRODUCT(N(A1:A22="D&quot,N(B1:B22=1))
(As a side issue, I had to force xl to evaluate true/false to a numeric value for this to work so if anyone else passing this thread knows why.....?)

However this also fails if it's changed to
=SUMPRODUCT(A:A="D",B:B=1)

I thought it may be because of referencing a column rather than a rnage of cells (I know they're the same thing but..) but other formulae work OK
eg =COUNTIF(B:B,1) works just fine

I know this gets you no nearer an answer so I'm just sharing thoughts!!

If you find an answer please post it back here.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[IvanMoala]

You can't use Array formulas (Ctrl + sht + Enter) on entire columns.

Ivan F Moala

http://www.xcelfiles.com

 

[KenWright]

Array formulas will not work on full Column or Row references, you have to set a range. This also holds true for the SUMPRODUCT function, which although it does not have to be array entered, works in exactly the same way as an Array formula.

You therefore cannot use B:B or 2:2 etc as references in your formulas.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Loomah]

Ivan, thanks for that.
Ken, thanks for both replies!!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[KenWright]

Wayne, just for the record, you are quite correct in that the reason you don't have to worry about the header is because you are performing a logical operation and determining whether or not it actually meets the criteria. This in itself is generating a set of boolean values so no problem there. Where PHV was coming from, is that if you were to perform a straight multiplication of two columns (No logical operation such as ="D" etc) within an array formula, then having text in there (ie a header) will cause it to bomb out, because you cannot multiply text and get any meaningful result.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Hi waynerenaud,

Lots of people are saying you can't do it. Yes that's true, but a bit trite. It has nothing directly to do with complete columns, and Ken is wrong to say that array formulas do not work with complete rows.

Formulas which work with arrays - and it's not always immediately obvious which they are (for example I posted earlier this morning about the MODE function) - are limited to a single dimension of the array being a maximum of 65535 (2^16 - 1), which just happens to be one less than the number of cells in a complete column.

They can work with arrays with many more elements than that in total; the limit is just on a single dimension so you should be able to use, for example A1:IV65535 in an array formula - although I'm not sure I'd recommend it.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[waynerenaud]

Wow - A great response. Thank you all for your help. I guess I will use a range (thinking in the back of my mind it may be easier to name the entire range and refer to the name). One thing is for sure.........the more you play, the more you realise you still have to learn
Thanks All

 

[xlbo]

Top tip Tony - Star for you

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[KenWright]

You are quite correct Tony, and my apologies for the bum steer on the rows. As far as the columns go though, it's semantics really as to whether you call it a complete column or a single range with more than 65,535 rows, as either way you can't use a complete column reference in any array formula, or the SUMPRODUCT function

Agreed entirely though that any array of that size is going to make it run like a dog, and hence I would be inclined to use a dynamic range reference to make sure you keep it as tight as possible, or if at all possible avoid using arrays at all on large data-sets.

Ref the max number of number of rows in an array, it's no coincidence that it just happens to be one less than 65,536 as both sets of numbers are a result of the fact that 16 bits will only allow 65,536 addresses (8 gives you 256, hence 256 columns), and hence the max number of rows, and one of the resultants being that as you rightly say, you need to reduce that number by one to be able to work with it with an array formula.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------