Will someone look at this and tell me why I have a #VALUE! error

H

hybridthinker

I looked back through it again and can't seem to figure out what the
problem is....so it will probably end up being an extra comma or
something...
 
A

Aladin Akyurek

It's better to use the comma syntax for doing a multiconditional summing
(when the shape of the ranges involved is identical)...

So...

=IF(OR(A3="",D3=""),"",SUMPRODUCT((WrkRecMonth=$A$1)*(WrkRecID=A3),(WrkRecGP
Hr)))

instead of

=IF(OR(A3="",D3=""),"",SUMPRODUCT((WrkRecMonth=$A$1)*(WrkRecID=A3)*(WrkRecGP
Hr)))

The comma syntax allows for text values like formula blanks ("") in the
range to sum, the star syntax obviously not.
 
R

Ron Rosenfeld

I'm confused...I've either been looking at code to long, or I don't know
how to fix it....the error is on the monthly and yearly report tabs.
Thanks in advance.

File Attached: http://www.excelforum.com/attachment.php?postid=318999 (payroll.zip)
Click to expand...


You are using the wrong syntax.

Syntax

SUMPRODUCT(array1,array2,array3, ...)

You are using:


=IF(OR(A3="",D3=""),"",SUMPRODUCT((WrkRecMonth=$A$1)*(WrkRecID=A3)*(WrkRecSalary)))

Try:


=IF(OR(A3="",D3=""),"",SUMPRODUCT((WrkRecMonth=$A$1),(WrkRecID=A3),(WrkRecGPHr)))




--ron
 
D

Don Guillett

When the formulas in the blank rows on the workrecord ws were deleted, it
works.
So, goto the row number indicator and clearcontents. However, this caused
ref on e2:d6 on yearly report so fix that first.
 
H

hybridthinker

I took everyone's advice and put it into the spread sheet. Now the
yearly report isn't carrying over any info. Also in the monthly
reports, if you select the month of June only one person's info shows
up...I truly appreciate everyone's help. Any ideas????

File Attached: http://www.excelforum.com/attachment.php?postid=319148 (updated payroll.zip)
 
A

Aladin Akyurek

There are many things that needs to be changed:

1) Dynamic named ranges are not defined properly.
2) You carry out expensive checks by computing the same thing twice.
3) You're using a SumProduct formula while a SumIf formula would suffice and
be more efficient.
4) In your updated version you didn't change all SumProduct formulas for
conditional summing to one that uses the comma syntax:

SUMPRODUCT((CondtionalExpression1)*(ConditionalExpression2),RangeToSum)
 
H

hybridthinker

thanks for looking...if you made the changes on a file could you please
repost....
 
H

hybridthinker

So...has anyone came up with some ideas? I see that 4 people have
downloaded the latest update...just curious...
 
T

Tom Ogilvy

I suspect most responders are reading this in the
microsoft.public.excel.worksheet.functions news group and can't post an
attachment back to the Excelforum.

Perhaps if you posted your email address, someone could email you the
corrected workbook.
 
H

hybridthinker

If you have done anywork to this file please forward it to
(e-mail address removed), if you fixed it fully or know how please just put
the fix in one cell and I will do the rest. I really don't know what
else to do...thats why I'm asking for help.
 
H

hybridthinker

I recieved two suggestions for solving my problems....I'm just wondering
if any of the 30 people who have downloaded it have come up with any of
their own conclusions?
 
D

dhascher

The problem with June not working in the blank lines in WorkRecord
worksheet. The range for you defined names is not enough rows. If the
cells in the enpty rows were not blank your code would work.

I think I got the formulas working by adding a N() around the
WrkRecMonth and WrkRecID arguments to SUMPRODUCT

From Monthly
=IF(OR(A4="",D4=""),"",SUMPRODUCT((WrkRecMonth=$A$1)*(WrkRecID=A4),(WrkRecSalary)))


From Yearly
=IF(OR(A2="",D2=""),"",SUMPRODUCT(N((WrkRecID=A2)),(WrkRecGPHr)))

I did not test the values to check if formulas were working correctly.
 
R

rpbale

I found that the N() with the conversion of the * to a , worked for me.
I never used the N() that way. Great idea!
 

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

Similar Threads

Work Rota 5
Multiple Option Controls w/in Worksheet 1
Using formulas to source a value based on 2 variables 2
VLOOKUP - Wrong Value Return 5
VLOOKUP Problem (limitation)? 3
Refering to other tabs easily 11
Take a look at my code and tell me what is wrong with this 4
Frank, I need your help, PLEASE 4

Top