Array Formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maggot_medwaysc@yahoo.co.uk - 04 Mar 2008 19:10 GMT

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

Reply to this Message

Bob Phillips - 04 Mar 2008 19:42 GMT

Try

=SUMPRODUCT(--(ActName=A8),--(Month=C1),Sheet1!E1:E1000)

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi
> hopefully someone can help!
[quoted text clipped - 29 lines]
> Thanks in advance
> Mags

Reply to this Message

Neeraj - 20 May 2008 05:57 GMT

Mr. Bob.

How are you doing? I hope fine.

I need your help.

i have 10 excel sheets in one work book. And in each excel sheets there is
some data.

the name of the sheets are as follows:

01-May, 02-May, 03-May, 06-May as so on....

Now i want to merge all the data in different excel sheet in which there is
only one sheet, i.e. monthly data..

THE EXCEL SHEET IS LIKE THIS:

A B C D E F G
H I

1 01May ?

2 02May ?

3 03May ?

4 04May ?

5 05May ?

6 06May ?

Now in place of the "?" mark ,,i want those data. So how i can link these
two excel different workbook.

Please help me.

Thanks & Regds

Neeraj

> Try
>
[quoted text clipped - 33 lines]
> > Thanks in advance
> > Mags

Reply to this Message

Ron Rosenfeld - 04 Mar 2008 19:51 GMT

>Hi
>hopefully someone can help!
[quoted text clipped - 29 lines]
>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 to this Message

maggot_medwaysc@yahoo.co.uk - 04 Mar 2008 20:44 GMT

Brilliant Thank you to both of you.

I'll push my luck now!, is there any easy way to get e.g. 01/02/08 or
01-Feb-08 to change to Feb so that in my previous example its sees
them both as the same thing and not formated to be that. I have tried
using =month(a1) but it doesnt always seem to return the correct
Month. So am having to type in the related month. This is ok except
if the core data changes it will not automatically update.

Thanks again for all your help

Mags

Reply to this Message

Ron Rosenfeld - 04 Mar 2008 21:02 GMT

>Brilliant Thank you to both of you.
>
[quoted text clipped - 8 lines]
>
>Mags

Let us assume that

C1: 2

for February.

You could then use MONTH(Month)=C1 to ensure the month is the same.
--ron

Reply to this Message

Bob Phillips - 04 Mar 2008 21:48 GMT

Maybe

=TEXT(A1,"mmm")

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Brilliant Thank you to both of you.
>
[quoted text clipped - 8 lines]
>
> Mags

Reply to this Message