OK, thanks. I addd the parentheses and now I get A #VALUE error, saying a
value in the formula is of the wrong data type. I messed with this yesterday
on another application and never could fix it. What is the deal with data
types? I checked the data types for this and they are either "general" or
currency where necessary.
Does Column I contain text values/formula blanks? If so, try...
=SUMPRODUCT(--('[RICHs
DESKTOP.xls]Invoices'!$H$4:$H$65536=$B6),--('[RICHs
DESKTOP.xls]Invoices'!$F$4:$F$65536=$B$2),--('[RICHs
DESKTOP.xls]Invoices'!$O$4:$O$65536="X"),'[RICHs
DESKTOP.xls]Invoices'!$I$4:$I$65536)
--
Domenic
http://www.xl-central.com
In article ,
Rich wrote:
OK, thanks. I addd the parentheses and now I get A #VALUE error, saying a
value in the formula is of the wrong data type. I messed with this yesterday
on another application and never could fix it. What is the deal with data
types? I checked the data types for this and they are either "general" or
currency where necessary.
"RagDyeR" wrote:
Just looking at your formula, the only thing I see is a pair of parens
missing:
=SUMPRODUCT((Invoices!$H$4:$H$65536=$B6)*(Invoices !$F$4:$F$65536=$B$2)*(Invo
ices!$O$4:$O$65536="X")*(Invoices!$I$4:$I$65536))
--
HTH,
RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===
"Rich" wrote in message
...
I am trying to add amounts from one workbook (Invoices) based on 3 criteria
and put the total in a second workbook (Jones cost control).
I have the following formula copied down the Total Paid column.
=SUMPRODUCT('[RICHs DESKTOP.xls]Invoices'!$H$4:$H$65536=$B6)*('[RICHs
DESKTOP.xls]Invoices'!$F$4:$F$65536=$B$2)*('[RICHs
DESKTOP.xls]Invoices'!$O$4:$O$65536="X")*('[RICHs
DESKTOP.xls]Invoices'!$I$4:$I$65536).
In the above formula Invoices H=Phase, Invoices F=Job, Invoices O=Paid (X)
&
Invoices I=Amount. The formual is in Jones cost control Total Paid column.
Invoices
Jones Cost Control
Job Phase Paid Amount Phase
Total Paid
Jones 5.1 x 1,000
4.2 400 (S/B)smith 5.1 x 500
5.1 1,250
Jones 4.2 x 400
Jones 4.2 600
Jones 5.1 X 250
Jones 5.1 700
It worked at one time. what is wrong with it? I'm not getting a formula
error...just a blank cell where the total should be.
Help. Thanks,