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

Try

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


--
---
HTH

Bob


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



wrote in message
...
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

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

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

On Tue, 4 Mar 2008 12:44:11 -0800 (PST), wrote:



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

Let us assume that

C1: 2

for February.

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

Maybe

=TEXT(A1,"mmm")

--
---
HTH

Bob


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



wrote in message
...


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

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

"Bob Phillips" wrote:

Try

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


--
---
HTH

Bob


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



wrote in message
...
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