Well...one correction, when I let the computer pick, it did not include the *

so I had to go back and fix it, but for the first time, I saw it work inter

sheet. Maybe now I can do this with the other file. Talk to you in another

3 months.

"Fred Smith" wrote:

All you need to do is specify your ranges properly.

Instead of:

=SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B")

Use:

=SUMPRODUCT(Sheet1!$A$2:$A$20="B")

Note in this formula, you specified the range properly the first time, but

not the second time:

=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1))

Use this instead:

=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:B $20=1))

The best way to insert ranges into a formula is to get Excel to do it.

Contrary to humans, Excel will specify the range properly every time. When

writing a formula, do the following (using your Sumproduct as an example):

Enter:

=sumproduct((

Now highlight the range you want to use. Go to Sheet2, and highlight cells

a2:a20. Watch the navigation bar, and you will see Excel insert the proper

range addresses. If you want an absolute address, press F4.

Continue entering the formula you want, as in:

="B")*(

Highlight your second range

Finish off the formula:

))

Regards,

Fred

"Altair1972m" wrote in message

...

Fred I found the problem. It does not seem to work between worksheets.

In sheet one I made 3 simple columns:

B 2 X

B 2 X

A 1 Y

B 1 Y

B 1 X

B 2 X

B 2 X

I ran the basic subproduct

=+SUMPRODUCT(($A1:$A20="B")*($B1:$B20=1)*($C1:$C20 ="X"))

and got - 1

Then I inserted a new sheet

I put in

=SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B")

Answer - 0

=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1))

=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$C$2:S heet2!C$20="X"))

Answer - #VALUE

I stopped there and copied this for you.

So...does sumproduct work across pages?

If not, do not feel like this was a complete waste of time, because I can

still eliminate most of my concatinations.

"Fred Smith" wrote:

We've sure been beating around the bush if all you want to do is sum a

column. Solutions to this problem are easily found.

What column do you want to sum? What are the conditions that need to be

met?

What version of Excel do you have?

Regards,

Fred

"Altair1972m" wrote in message

...

Fred, I've been playing with this on an off for months and I think I

see

my

confusion:

I was trying to sum the contents of a column. Was this command you

were

instructing designed to sum the column or PROVIDE ME with the judgement

call

on whether this column should be part of the summation?

"Fred Smith" wrote:

1. To determine whether to use 'Client List' or 'Client_List', look at

the

name of the spreadsheet on the tabs at the bottom. The name you use in

your

formulas must match the name on the tab of the spreadsheet.

2. I had trouble with the copy and paste as well. When I created the

formula, I simply modified the one you has posted. However, when I

pasted

that into Excel, it complained about the quotes. They weren't regular

quotes, but smart quotes, which Excel doesn't like. When I changed the

quotes, Excel accepted the formula. Here's a copy of what I used:

=SUMPRODUCT(('Client List'!A$2:A$60000=E$2)*('Client

List'!B$2:B$60000=$A4)*('Client List'!C$2:C$60000=$B4)*('Client

List'!I$2:I$60000="R"))

Remember, if this appears on more than one line in your message, you

will

need to delete the word wrap.

Regards,

Fred.

"Altair1972m" wrote in message

...

Fred I SO appreciate the effort.

I'm just not used to being this success impaired.

I cut and pasted your formula in and then added the + at the

beginning.

It

still says there is a problem with the first array. And I even

tried

changing 'Client List' to 'Client_List' but that didn't work.

"Fred Smith" wrote:

You are missing the arithmetic operator, and have extraneous

commas.

Try

the

following:

Sumproduct(('Client List'!A$2:A$60000=E$2)*(‘Client List’!B$2:B$60000=$A4)*(‘Client

List’!C$2:C$60000=$B4)*(‘Client List’!I$2:I$60000=”R”))

Regards,

Fred

"Altair1972m" wrote in

message

...

I think I found the problem: Can Sumproduct compare cell content

to

another

cell content?

I am so game for this, but I have been trying to play with the

command

on

and off for 2 days (on work time) and I'm not getting anywhere. I

feel

so

dee

dee deeeeee here.

I’m trying

Sumproduct(('Client List'!A$2:A$60000,=E$2)(‘Client

List’!B$2:B$60000,=$A4)(‘Client List’!C$2:C$60000,=$B4)( ‘Client

List’!I$2:I$60000,=”R”)

Client List Column A is the dates. Client List Column B is the

volags.

Client List C is the County, Client List I is the Status as

refugee.

On the Totals sheet the dates run across Row 2. Totals of the

year

are

the

next row using the sum command, and then the next 12 rows list

the

monthy

totals with the criteria in A, B, and C

The formula above is In Cell E4 which is under the 2008-01 date,

the

USCC

VOLAG in Atlantic County

Here is the total table

2008-01

0

USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(‘Client

List’!B$2:B$60000,=$A4)(‘Client List’!C$2:C$60000,=$B4)( ‘Client

List’!I$2:I$60000,=”R”)

USCC CAMDEN 0

USCC ESSEX 0

USCC PASSAIC 0

USCC MERCER 0

LIRS ESSEX 0

LIRS MERCER 0

JFS BERGEN 0

JVS ESSEX 0

JFVS MIDDLESEX 0

IRC UNION 0

IRSA HUDSON 0

I put it in and got an error.

I assumed I had a problem with the date, so I tried the formula

again

this

time without the reference to clientlist A

Still have an error.

I think I found the problem: Can Sumproduct compare to a cell

content?

So then I tried this:

+Sumproduct((‘Client List’!B$2:B$60000,="USCC")(‘Client

List’!C$2:C$60000,="ATLANTIC")( ‘Client List’!I$2:I$60000,=”R”))

Just to see if that was the problem. I still had an error.

Help please!

"Sometimes the genius and the idiot are the same person"

--Nitchie

"Fred Smith" wrote:

There's no reason Sumproduct won't work in this situation.

You should spend some time getting to know it, as it's a very

useful

function. It's worth every minute you spend on it.

By the way, the pluses in your formulae are superfluous.

=S3&YEAR(A3)&MONTH(A3)

works just as well, and will be easier for others to understand.

Regards,

Fred.

"Altair1972m" wrote in

message

...

"Teethless mama" wrote:

=SUMPRODUCT((A1:A100="x")*(B1:B100="y"))

"Trish" wrote:

I am trying to use the count function where two conditions

in

different

columns need to be satisfied and having no luck. Basically

I

want

to

look in

the first column to see if it's A, then look in 2nd column

to

see

if

it's B,

then count it... is that possible?

I had this same question earlier in the year. I just tried to

plug

in

the

sumproduct command and it didn't work for me.

I'm going to give you a much longer method of doing it which

was

my

work

around.

I had 4 categories to marry:

Status of Client

Date of Arrival

VOLAG

County

And what I was doing was counting all clients arriving during

a

month

from

a

volag in certain counties on a separate page.