Need help with SUMPRODUCT Formula

[Rich]

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,

 

[RagDyeR]

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)*(Invoices!$O$4:$O$65536="X")*(Invoices!$I$4:$I$65536))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



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,

 

[RagDyeR]

Your data example is garbled, BUT ... why is the "x" (Paid) displayed
*before* the Amount?

If "x" is in Column O, and "Amount" is in Column I, shouldn't the "x"
display last?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

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)*(Invoices!$O$4:$O$65536="X")*(Invoices!$I$4:$I$65536))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



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,

 

[Domenic]

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,

Maybe the formula returns a zero, but the cell containing the formula is
custom formatted to hide zero values. Is this the case?

 

[Rich]

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.

 

[Domenic]

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

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.

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



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,

 

[RagDyeR]

If the column that you're totaling, Column I, is populated by either keying
in, or a formula that returns zero (0), both of which are TRUE numbers, your
formula should work.

However, if you're populating it with a formula that might return blanks (
"" ), OR importing data from another app, then that column can contain data
that is not recognized by XL as True numbers, since the blank ( "" ) is
really text, and the imports may contain invisible characters.

If that might be the case, try this form of Sumproduct:

=SUMPRODUCT((Invoices!$H$4:$H$65536=$B6)*(Invoices!$F$4:$F$65536=$B$2)*(Invoices!$O$4:$O$65536="X"),(Invoices!$I$4:$I$65536))

Where the asterisk is replaced with a comma before the totaling Column I
reference.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


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.

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)*(Invoices!$O$4:$O$65536="X")*(Invoices!$I$4:$I$65536))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



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,

 

[Rich]

Column I has the amounts (currency).

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

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.

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



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,

 

[Rich]

The column contains currency numbers that I enter manually. There is nothing
else in that column.

If the column that you're totaling, Column I, is populated by either keying
in, or a formula that returns zero (0), both of which are TRUE numbers, your
formula should work.

However, if you're populating it with a formula that might return blanks (
"" ), OR importing data from another app, then that column can contain data
that is not recognized by XL as True numbers, since the blank ( "" ) is
really text, and the imports may contain invisible characters.

If that might be the case, try this form of Sumproduct:

=SUMPRODUCT((Invoices!$H$4:$H$65536=$B6)*(Invoices!$F$4:$F$65536=$B$2)*(Invoices!$O$4:$O$65536="X"),(Invoices!$I$4:$I$65536))

Where the asterisk is replaced with a comma before the totaling Column I
reference.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


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.

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)*(Invoices!$O$4:$O$65536="X")*(Invoices!$I$4:$I$65536))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



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,

 

[Domenic]

The column contains currency numbers that I enter manually. There is nothing

else in that column.

Do any of the cells in any column contain #VALUE! ?

--
Domenic
http://www.xl-central.com

Column I has the amounts (currency).

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

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.

:

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



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,

 

[RagDyeR]

Did you try the last formula I suggested?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

The column contains currency numbers that I enter manually. There is
nothing
else in that column.

If the column that you're totaling, Column I, is populated by either
keying
in, or a formula that returns zero (0), both of which are TRUE numbers,
your
formula should work.

However, if you're populating it with a formula that might return blanks (
"" ), OR importing data from another app, then that column can contain
data
that is not recognized by XL as True numbers, since the blank ( "" ) is
really text, and the imports may contain invisible characters.

If that might be the case, try this form of Sumproduct:

=SUMPRODUCT((Invoices!$H$4:$H$65536=$B6)*(Invoices!$F$4:$F$65536=$B$2)*(Invoices!$O$4:$O$65536="X"),(Invoices!$I$4:$I$65536))

Where the asterisk is replaced with a comma before the totaling Column I
reference.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


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.

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)*(Invoices!$O$4:$O$65536="X")*(Invoices!$I$4:$I$65536))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



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,