Thread
:
Array Formulas
View Single Post
#
3
Posted to microsoft.public.excel.programming
Ron Rosenfeld
external usenet poster
Posts: 5,651
Array Formulas
On Tue, 4 Mar 2008 11:10:53 -0800 (PST),
wrote:
Hi
hopefully someone can help!
Im trying to return a sumif using two constants, Desc & Month
Act Name Month
Column A Column B Column E
Hat Feb 08 1000.00
T Shirt Mar 08 2500.00
Hat Mar 08 2300.00
I have tried various examples and they always seem to work, but when I
try using the formula given within my own spreadsheet I get a #Num
error.
=SUM(IF((ActName=A8)*(Month=C1),Sheet1!E1:E1000))
The core data is quite long 500 rows so I have named the columns
(ActName, Month) although I Havent named the column with the numbers
in that I want to sum
The only difference I can see between my sheet and the many examples,
is that column A & B on mine are links from other workbooks. And I
need the result on a different sheet, as I am trying to summarise a
cashflow
Is this the problem and if so does anyone know how to get round it.
I had never used array formulas until I started investigating how to
do this so I may be going about it the completely wrong way.
Any help would be gratefully received.
Thanks in advance
Mags
I don't understand the #NUM error; I wouldn't be surprised with other kinds of
errors, though.
Some potential areas to investigate:
1. What is in C1?
2. Are the contents of C1 and of Month compatible -- e.g. are they both text
strings, or is one an Excel date formatted to look like Feb 08, and the other a
text string?
2. Are the dimensions of ActName and Month both 1000 rows?
2. Are you entering this as an ARRAY formula? (Hold down <ctrl<shift while
hitting <enter. Excel will place braces {...} around the formula if you did
it correctly).
--ron
Reply With Quote
Ron Rosenfeld
View Public Profile
Find all posts by Ron Rosenfeld