Help please, IF statement/SUMIF statement

G

Guest

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?
 
G

Guest

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?
 
G

Guest

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?
 
G

Guest

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!
 
G

Guest

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.
 
G

Guest

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.
 
J

JE McGimpsey

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

It's hard to troubleshoot with vague information.
 
J

JE McGimpsey

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...
 
G

Guest

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?
 
J

JE McGimpsey

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...
 
G

Guest

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
 
G

Guest

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.
 
J

JulieD

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 ...

are you sure you don't want

Cheers
JulieD
 
G

Guest

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?
 
G

Guest

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top