Hi There,

Would anyone be able to tell me why I am getting a #VALUE! error on the
following function;

=SUMPRODUCT(--('CHANGE ORDER LOG'!$E$7:$N$7="P"),--('CHANGE ORDER
LOG'!$E$7:$N$7<""),--('CHANGE ORDER LOG'!$D$1:$D$10000="*Hours"),--('CHANGE
ORDER LOG'!$D$1:$D$10000<""),('CHANGE ORDER LOG'!$O$1:$O$10000))

Thanks so much
--
CK

Hi,

All your ranges in sumproduct must be the same size so the first condition
for E7 - N7 is throwing the formula. In addition you check e7 - n7 twice.
Once for "P" and a second time to check it's <"". While this won't actually
give a problem it's unnecessary. You dod the same for the second check on D1
- D1000

If you could explain what your trying to do I'm sure someone will give you a
working version of your formula.

Mike



"ColleenK" wrote:

Hi There,

Would anyone be able to tell me why I am getting a #VALUE! error on the
following function;

=SUMPRODUCT(--('CHANGE ORDER LOG'!$E$7:$N$7="P"),--('CHANGE ORDER
LOG'!$E$7:$N$7<""),--('CHANGE ORDER LOG'!$D$1:$D$10000="*Hours"),--('CHANGE
ORDER LOG'!$D$1:$D$10000<""),('CHANGE ORDER LOG'!$O$1:$O$10000))

Thanks so much
--
CK

This is what I am trying to do. Columns E thru N are the headings for each
new change order, if one of these columns has a "P" in row 7 then I want the
formuls to look down column D and find any row that has the word "hours" in
it, then sum the corresponding data in column O. Hope this helps.
--
CK


"Mike H" wrote:

Hi,

All your ranges in sumproduct must be the same size so the first condition
for E7 - N7 is throwing the formula. In addition you check e7 - n7 twice.
Once for "P" and a second time to check it's <"". While this won't actually
give a problem it's unnecessary. You dod the same for the second check on D1
- D1000

If you could explain what your trying to do I'm sure someone will give you a
working version of your formula.

Mike



"ColleenK" wrote:

Hi There,

Would anyone be able to tell me why I am getting a #VALUE! error on the
following function;

=SUMPRODUCT(--('CHANGE ORDER LOG'!$E$7:$N$7="P"),--('CHANGE ORDER
LOG'!$E$7:$N$7<""),--('CHANGE ORDER LOG'!$D$1:$D$10000="*Hours"),--('CHANGE
ORDER LOG'!$D$1:$D$10000<""),('CHANGE ORDER LOG'!$O$1:$O$10000))

Thanks so much
--
CK

Hi,

maybe this

=IF(COUNTIF(E7:N7,"p")0,SUMPRODUCT((D1:D1000="Hou rs")*(O1:O1000)),"")

Mike

"ColleenK" wrote:

This is what I am trying to do. Columns E thru N are the headings for each
new change order, if one of these columns has a "P" in row 7 then I want the
formuls to look down column D and find any row that has the word "hours" in
it, then sum the corresponding data in column O. Hope this helps.
--
CK


"Mike H" wrote:

Hi,

All your ranges in sumproduct must be the same size so the first condition
for E7 - N7 is throwing the formula. In addition you check e7 - n7 twice.
Once for "P" and a second time to check it's <"". While this won't actually
give a problem it's unnecessary. You dod the same for the second check on D1
- D1000

If you could explain what your trying to do I'm sure someone will give you a
working version of your formula.

Mike



"ColleenK" wrote:

Hi There,

Would anyone be able to tell me why I am getting a #VALUE! error on the
following function;

=SUMPRODUCT(--('CHANGE ORDER LOG'!$E$7:$N$7="P"),--('CHANGE ORDER
LOG'!$E$7:$N$7<""),--('CHANGE ORDER LOG'!$D$1:$D$10000="*Hours"),--('CHANGE
ORDER LOG'!$D$1:$D$10000<""),('CHANGE ORDER LOG'!$O$1:$O$10000))

Thanks so much
--
CK

Can you use a wildcard in the sumproduct function i.e.
SUMPRODUCT((D1:D1000="*Hours")*(O1:O1000)),"")
--
CK


"Mike H" wrote:

Hi,

maybe this

=IF(COUNTIF(E7:N7,"p")0,SUMPRODUCT((D1:D1000="Hou rs")*(O1:O1000)),"")

Mike

"ColleenK" wrote:

This is what I am trying to do. Columns E thru N are the headings for each
new change order, if one of these columns has a "P" in row 7 then I want the
formuls to look down column D and find any row that has the word "hours" in
it, then sum the corresponding data in column O. Hope this helps.
--
CK


"Mike H" wrote:

Hi,

All your ranges in sumproduct must be the same size so the first condition
for E7 - N7 is throwing the formula. In addition you check e7 - n7 twice.
Once for "P" and a second time to check it's <"". While this won't actually
give a problem it's unnecessary. You dod the same for the second check on D1
- D1000

If you could explain what your trying to do I'm sure someone will give you a
working version of your formula.

Mike



"ColleenK" wrote:

Hi There,

Would anyone be able to tell me why I am getting a #VALUE! error on the
following function;

=SUMPRODUCT(--('CHANGE ORDER LOG'!$E$7:$N$7="P"),--('CHANGE ORDER
LOG'!$E$7:$N$7<""),--('CHANGE ORDER LOG'!$D$1:$D$10000="*Hours"),--('CHANGE
ORDER LOG'!$D$1:$D$10000<""),('CHANGE ORDER LOG'!$O$1:$O$10000))

Thanks so much
--
CK

Hi,

Not directly no but you can do it like this

=IF(COUNTIF(E7:N7,"p")0,SUMPRODUCT((ISNUMBER(SEAR CH("Hours",D1:D1000)))*(O1:O1000)),"")

Mike

"ColleenK" wrote:

Can you use a wildcard in the sumproduct function i.e.
SUMPRODUCT((D1:D1000="*Hours")*(O1:O1000)),"")
--
CK


"Mike H" wrote:

Hi,

maybe this

=IF(COUNTIF(E7:N7,"p")0,SUMPRODUCT((D1:D1000="Hou rs")*(O1:O1000)),"")

Mike

"ColleenK" wrote:

This is what I am trying to do. Columns E thru N are the headings for each
new change order, if one of these columns has a "P" in row 7 then I want the
formuls to look down column D and find any row that has the word "hours" in
it, then sum the corresponding data in column O. Hope this helps.
--
CK


"Mike H" wrote:

Hi,

All your ranges in sumproduct must be the same size so the first condition
for E7 - N7 is throwing the formula. In addition you check e7 - n7 twice.
Once for "P" and a second time to check it's <"". While this won't actually
give a problem it's unnecessary. You dod the same for the second check on D1
- D1000

If you could explain what your trying to do I'm sure someone will give you a
working version of your formula.

Mike



"ColleenK" wrote:

Hi There,

Would anyone be able to tell me why I am getting a #VALUE! error on the
following function;

=SUMPRODUCT(--('CHANGE ORDER LOG'!$E$7:$N$7="P"),--('CHANGE ORDER
LOG'!$E$7:$N$7<""),--('CHANGE ORDER LOG'!$D$1:$D$10000="*Hours"),--('CHANGE
ORDER LOG'!$D$1:$D$10000<""),('CHANGE ORDER LOG'!$O$1:$O$10000))

Thanks so much
--
CK