Okay, I am trying to do the following:
I am trying to set up a function that if 4 columns match then sum the 5th
column. It needs to check about 5000 lines with this formula. I don't know
how to set it up to where it checks all statements. Can someone please help
me with the proper formula?

Just to clarify...
if A1 of current=A1 of another sheet, and B1 of current sheet=B2 of another
sheet, and etc. for 4 columns, then sum F5.... then I need it to check 5000
lines...

What is the proper formula for this?

"Brad_A" wrote:

Okay, I am trying to do the following:
I am trying to set up a function that if 4 columns match then sum the 5th
column. It needs to check about 5000 lines with this formula. I don't know
how to set it up to where it checks all statements. Can someone please help
me with the proper formula?

One way:

=SUMPRODUCT(--(A1:A5000=Sheet2!A1:A5000), --(B1:B5000=Sheet2!B1:B5000),
--(C1:C5000=Sheet2!C1:C5000), --(D1:D5000=Sheet2!D1:D5000), E1:E5000)


for an explanation of the usage of "--" see

http://www.mcgimpsey.com/excel/doubleneg.html



In article ,
Brad_A wrote:

Just to clarify...
if A1 of current=A1 of another sheet, and B1 of current sheet=B2 of another
sheet, and etc. for 4 columns, then sum F5.... then I need it to check 5000
lines...

What is the proper formula for this?

"Brad_A" wrote:

Okay, I am trying to do the following:
I am trying to set up a function that if 4 columns match then sum the 5th
column. It needs to check about 5000 lines with this formula. I don't
know
how to set it up to where it checks all statements. Can someone please
help
me with the proper formula?

I have tried this, but it is not working

=SUMPRODUCT((I5='Other Sheet '!B2:B5000)*(K5='Other Sheet
'!C2:C5000)*(O5='Other Sheet '!E2:E5000)*('Other Sheet
'!G2:G5000="2006")*(Other Sheet '!H2:H5000="1")*('Other Sheet '!I2:I5000))

It is not summing the I column from the Other Sheet even when all the
criteria match. What do I have to do?


"Brad_A" wrote:

Okay, I am trying to do the following:
I am trying to set up a function that if 4 columns match then sum the 5th
column. It needs to check about 5000 lines with this formula. I don't know
how to set it up to where it checks all statements. Can someone please help
me with the proper formula?

I can't get any of the formulas to work... can someone help please?

I am trying to add all of column I if 5 criteria match. I have tried the
sum product and it isn't working. I really need help with this ASAP.

Thanks!

"Brad_A" wrote:

Okay, I am trying to do the following:
I am trying to set up a function that if 4 columns match then sum the 5th
column. It needs to check about 5000 lines with this formula. I don't know
how to set it up to where it checks all statements. Can someone please help
me with the proper formula?

Hi,

I presume "2006" and "1" is right and (Other is ('Other in the formula.

Ola

yes and yes

I don't know why it isn't working and I am under heavy pressure to get it to
work. I really need help from someone.

"Ola" wrote:

Hi,

I presume "2006" and "1" is right and (Other is ('Other in the formula.

Ola

I am getting an error to enter in a number between -999,999,999 and
999,999,999 with this formula

=SUMPRODUCT((I5='Other Sheet '!B2:B5000),(K5='Other Sheet
'!C2:C5000),(O5='Other Sheet '!E2:E5000),('Other Sheet
'!G2:G5000="2006"),('Other Sheet '!H2:H5000="1"),'Other Sheet '!I2:I5000)

Obviously something is wrong with the formula. I need the formula to check
account number, department, product number, year, and period. The year and
period is marked with the "2006" and the "1". The other is matching up one
sheet of data with another sheet of data. If all columns match, I need for
it to add the sum up of all those categories and put it in the period cell.
So, if I am in period 1 of 2006, I would want it to sum up all of the things
for that period that matches all 4 criteria.

I don't know how else to explain it. I really need help here.


"Brad_A" wrote:

yes and yes

I don't know why it isn't working and I am under heavy pressure to get it to
work. I really need help from someone.

"Ola" wrote:

Hi,

I presume "2006" and "1" is right and (Other is ('Other in the formula.

Ola

What does "not working" mean to you? Are you getting wrong results?
Errors? crashes?

It's hard to troubleshoot with vague information.


In article ,
Brad_A wrote:

I can't get any of the formulas to work... can someone help please?

I am trying to add all of column I if 5 criteria match. I have tried the
sum product and it isn't working. I really need help with this ASAP.

Errors... please continue to read all my messages

"JE McGimpsey" wrote:

What does "not working" mean to you? Are you getting wrong results?
Errors? crashes?

It's hard to troubleshoot with vague information.


In article ,
Brad_A wrote:

I can't get any of the formulas to work... can someone help please?

I am trying to add all of column I if 5 criteria match. I have tried the
sum product and it isn't working. I really need help with this ASAP.

At least one thing that's wrong is that you didn't coerce the TRUE/FALSE
arrays to numeric 1/0. Try:

=SUMPRODUCT(--(I5='Other Sheet '!B2:B5000),--(K5='Other Sheet
'!C2:C5000),--(O5='Other Sheet '!E2:E5000),--('Other Sheet
'!G2:G5000="2006"),--('Other Sheet '!H2:H5000="1"),'Other Sheet
'!I2:I5000)

Other things to look for:

"2006" and "1" are text, so the values in your list are expected to be
text, rather than numbers, too...

In article ,
Brad_A wrote:

I am getting an error to enter in a number between -999,999,999 and
999,999,999 with this formula

=SUMPRODUCT((I5='Other Sheet '!B2:B5000),(K5='Other Sheet
'!C2:C5000),(O5='Other Sheet '!E2:E5000),('Other Sheet
'!G2:G5000="2006"),('Other Sheet '!H2:H5000="1"),'Other Sheet '!I2:I5000)

Obviously something is wrong with the formula. I need the formula to check
account number, department, product number, year, and period. The year and
period is marked with the "2006" and the "1". The other is matching up one
sheet of data with another sheet of data. If all columns match, I need for
it to add the sum up of all those categories and put it in the period cell.
So, if I am in period 1 of 2006, I would want it to sum up all of the things
for that period that matches all 4 criteria.

I don't know how else to explain it. I really need help here.

I am using this formula... took out the period and year for now just to see
if I can get the formula to work.. It isn't working right now... when I press
enter, it opens up a thing to find a file. Also, it puts a - in the cell. I
matched up all the categories also to get it to match.

=SUMPRODUCT(--(I5='Ferguson 06 Budget '!B2:B5000),--(K5='Ferguson 06 Budget
'!C2:C5000),--(O5='Ferguson 06 Budget '!E2:E5000),'Ferguson 06 Budget
'!I2:I5000)

What else should I do?

"JE McGimpsey" wrote:

What does "not working" mean to you? Are you getting wrong results?
Errors? crashes?

It's hard to troubleshoot with vague information.


In article ,
Brad_A wrote:

I can't get any of the formulas to work... can someone help please?

I am trying to add all of column I if 5 criteria match. I have tried the
sum product and it isn't working. I really need help with this ASAP.

Where did you go? Is there a number I can call for support for this?


"JE McGimpsey" wrote:

What does "not working" mean to you? Are you getting wrong results?
Errors? crashes?

It's hard to troubleshoot with vague information.


In article ,
Brad_A wrote:

I can't get any of the formulas to work... can someone help please?

I am trying to add all of column I if 5 criteria match. I have tried the
sum product and it isn't working. I really need help with this ASAP.

Check your worksheet name. Do you really have a space after Budget?

In article ,
Brad_A wrote:

I am using this formula... took out the period and year for now just to see
if I can get the formula to work.. It isn't working right now... when I press
enter, it opens up a thing to find a file. Also, it puts a - in the cell. I
matched up all the categories also to get it to match.

=SUMPRODUCT(--(I5='Ferguson 06 Budget '!B2:B5000),--(K5='Ferguson 06 Budget
'!C2:C5000),--(O5='Ferguson 06 Budget '!E2:E5000),'Ferguson 06 Budget
'!I2:I5000)

What else should I do?

I went back to work...

This is not a Microsoft support site - it's a peer-to-peer newsgroup.
Everyone (or nearly everyone) who posts here is another (volunteer)
customer, not a Microsoft employee.

You can call Microsoft tech support, but you'll have to pay for it...



In article ,
Brad_A wrote:

Where did you go? Is there a number I can call for support for this?

Hi Brad,

Here are some Error Checking Methods:

1. Limit the number of records in the formula from 5000 to...1... 5...
50....500... Search for "Errors".

2. Find Errors by Error Checking:
Menu: Tools/Error Checking...
If there is no error just a 0 or blank. Try Tools/Formula Auditing/Evaluate
formula.
(This tool varies from useless or great.)

3. Find Errors by GoTo: (similar to No 2)
a) Mark all your records
b) Menu: Edit/GoTo...Special select Formula + Errors or anything else that
is important.

4. Menu: Insert/Function... Look at the calculated numbers on the right side.
(you probably used this before)

5. The F9 Method:
Press F2. Mark selected Parts in your formula and press F9. This will make
Excel calculate bits of your formula. (Use Esc to abort).

These are just a few suggestions.

Regards,
Ola

Yes... that cell was referenced with a direct click on it. I still haven't
figured out where my error is, and I have reviewed it as much as possible.

"JE McGimpsey" wrote:

Check your worksheet name. Do you really have a space after Budget?

In article ,
Brad_A wrote:

I am using this formula... took out the period and year for now just to see
if I can get the formula to work.. It isn't working right now... when I press
enter, it opens up a thing to find a file. Also, it puts a - in the cell. I
matched up all the categories also to get it to match.

=SUMPRODUCT(--(I5='Ferguson 06 Budget '!B2:B5000),--(K5='Ferguson 06 Budget
'!C2:C5000),--(O5='Ferguson 06 Budget '!E2:E5000),'Ferguson 06 Budget
'!I2:I5000)

What else should I do?

Hi Brad

haven't seen any of the previous converstation but the thing that strikes me
about the formula is the order of the elements ...

=SUMPRODUCT(--(I5='Ferguson 06 Budget '!B2:B5000),--(K5='Ferguson 06
Budget
'!C2:C5000),--(O5='Ferguson 06 Budget '!E2:E5000),'Ferguson 06 Budget
'!I2:I5000)

are you sure you don't want

=SUMPRODUCT(--(06 Budget '!B2:B5000=I5),--('Ferguson 06 Budget
'!C2:C5000=K5),--('Ferguson 06 Budget '!E2:E5000=O5),'Ferguson 06
Budget
'!I2:I5000)

Cheers
JulieD


"Brad_A" wrote in message
...
Yes... that cell was referenced with a direct click on it. I still
haven't
figured out where my error is, and I have reviewed it as much as possible.

"JE McGimpsey" wrote:

Check your worksheet name. Do you really have a space after Budget?

In article ,
Brad_A wrote:

I am using this formula... took out the period and year for now just to
see
if I can get the formula to work.. It isn't working right now... when I
press
enter, it opens up a thing to find a file. Also, it puts a - in the
cell. I
matched up all the categories also to get it to match.

=SUMPRODUCT(--(I5='Ferguson 06 Budget '!B2:B5000),--(K5='Ferguson 06
Budget
'!C2:C5000),--(O5='Ferguson 06 Budget '!E2:E5000),'Ferguson 06 Budget
'!I2:I5000)

What else should I do?

I limited the formula and I am still getting a dash in the cell.

I also fixed it to ensure that there is no spaces in the sheet names after
the name.

This is my current formula:
=SUMPRODUCT(--(I5='Ferguson 06 Budget'!B2:B5),--(K5='Ferguson 06
Budget'!C2:C5),--(O5='Ferguson 06 Budget'!E2:E5),'Ferguson 06 Budget'!I2:I5).

Any other thoughts or suggestions?

"Ola" wrote:

Hi Brad,

Here are some Error Checking Methods:

1. Limit the number of records in the formula from 5000 to...1... 5...
50....500... Search for "Errors".

2. Find Errors by Error Checking:
Menu: Tools/Error Checking...
If there is no error just a 0 or blank. Try Tools/Formula Auditing/Evaluate
formula.
(This tool varies from useless or great.)

3. Find Errors by GoTo: (similar to No 2)
a) Mark all your records
b) Menu: Edit/GoTo...Special select Formula + Errors or anything else that
is important.

4. Menu: Insert/Function... Look at the calculated numbers on the right side.
(you probably used this before)

5. The F9 Method:
Press F2. Mark selected Parts in your formula and press F9. This will make
Excel calculate bits of your formula. (Use Esc to abort).

These are just a few suggestions.

Regards,
Ola

Julie,

I changed the formula to the following... as a test, I renamed the sheet
that it is checking and retrieving information from to Test.

=SUMPRODUCT(--(Test!B2:B5=I5),--(Test!C2:C5=K5),--(Test!E2:E5=O5),Test!I2:I5)

The result is a - in the cell. The Cell is R5 on the current sheet. Any
other suggestions?



"JulieD" wrote:

Hi Brad

haven't seen any of the previous converstation but the thing that strikes me
about the formula is the order of the elements ...

=SUMPRODUCT(--(I5='Ferguson 06 Budget '!B2:B5000),--(K5='Ferguson 06
Budget
'!C2:C5000),--(O5='Ferguson 06 Budget '!E2:E5000),'Ferguson 06 Budget
'!I2:I5000)

are you sure you don't want

=SUMPRODUCT(--(06 Budget '!B2:B5000=I5),--('Ferguson 06 Budget
'!C2:C5000=K5),--('Ferguson 06 Budget '!E2:E5000=O5),'Ferguson 06
Budget
'!I2:I5000)

Cheers
JulieD


"Brad_A" wrote in message
...
Yes... that cell was referenced with a direct click on it. I still
haven't
figured out where my error is, and I have reviewed it as much as possible.

"JE McGimpsey" wrote:

Check your worksheet name. Do you really have a space after Budget?

In article ,
Brad_A wrote:

I am using this formula... took out the period and year for now just to
see
if I can get the formula to work.. It isn't working right now... when I
press
enter, it opens up a thing to find a file. Also, it puts a - in the
cell. I
matched up all the categories also to get it to match.

=SUMPRODUCT(--(I5='Ferguson 06 Budget '!B2:B5000),--(K5='Ferguson 06
Budget
'!C2:C5000),--(O5='Ferguson 06 Budget '!E2:E5000),'Ferguson 06 Budget
'!I2:I5000)

What else should I do?

Hi,

If the Cell Format is correct, the F9"-Error Search" don't give any strange
values; Reduce the formula (fewer criterias or rows) until it start to work.
If it still don't work, try to rewrite every Text and Number - in your test
sample.

Ola

Ola,

I hit F9 and it didn't do anything. I also condensed the formula down to
the following:


=SUMPRODUCT(--(Test!B2:B5=I5),--(Test!C2:C5=K5),--(Test!E2:E5=O5),Test!I2:I5)

This is still leaving - in the cell.

"Ola" wrote:

Hi,

If the Cell Format is correct, the F9"-Error Search" don't give any strange
values; Reduce the formula (fewer criterias or rows) until it start to work.
If it still don't work, try to rewrite every Text and Number - in your test
sample.

Ola

Brad,

Try and ask a new question.
"How can SUMPRODUCT give me - as an answer?"

Ola

Hi Brad

okay with text values in B2:B5, and C2:C5 and E2:E5 and numeric values in
I2:I5 on a sheet called test
and with the criteria in I5, K5, O5 of the current sheet it worked for me.

are you sure that there are not things like trailing spaces in you ranges
which means that nothing matches the criteria?
if you've using ver 2002 / 2003 what does running the formula through tools
/ formula auditing / evaluate formula show?

Cheers
JulieD

"Brad_A" wrote in message
...
Julie,

I changed the formula to the following... as a test, I renamed the sheet
that it is checking and retrieving information from to Test.

=SUMPRODUCT(--(Test!B2:B5=I5),--(Test!C2:C5=K5),--(Test!E2:E5=O5),Test!I2:I5)

The result is a - in the cell. The Cell is R5 on the current sheet. Any
other suggestions?



"JulieD" wrote:

Hi Brad

haven't seen any of the previous converstation but the thing that strikes
me
about the formula is the order of the elements ...

=SUMPRODUCT(--(I5='Ferguson 06 Budget '!B2:B5000),--(K5='Ferguson 06
Budget
'!C2:C5000),--(O5='Ferguson 06 Budget '!E2:E5000),'Ferguson 06
Budget
'!I2:I5000)

are you sure you don't want

=SUMPRODUCT(--(06 Budget '!B2:B5000=I5),--('Ferguson 06 Budget
'!C2:C5000=K5),--('Ferguson 06 Budget '!E2:E5000=O5),'Ferguson 06
Budget
'!I2:I5000)

Cheers
JulieD


"Brad_A" wrote in message
...
Yes... that cell was referenced with a direct click on it. I still
haven't
figured out where my error is, and I have reviewed it as much as
possible.

"JE McGimpsey" wrote:

Check your worksheet name. Do you really have a space after Budget?

In article ,
Brad_A wrote:

I am using this formula... took out the period and year for now just
to
see
if I can get the formula to work.. It isn't working right now...
when I
press
enter, it opens up a thing to find a file. Also, it puts a - in the
cell. I
matched up all the categories also to get it to match.

=SUMPRODUCT(--(I5='Ferguson 06 Budget '!B2:B5000),--(K5='Ferguson 06
Budget
'!C2:C5000),--(O5='Ferguson 06 Budget '!E2:E5000),'Ferguson 06
Budget
'!I2:I5000)

What else should I do?