SUMPRODUCT

[ColleenK]

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

 

[Mike H]

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]

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.

 

[Mike H]

Hi,

maybe this

=IF(COUNTIF(E7:N7,"p")>0,SUMPRODUCT((D11000="Hours")*(O1:O1000)),"")

Mike

 

[ColleenK]

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

 

[Mike H]

Hi,

Not directly no but you can do it like this

=IF(COUNTIF(E7:N7,"p")>0,SUMPRODUCT((ISNUMBER(SEARCH("Hours",D11000)))*(O1:O1000)),"")

Mike

 

[ColleenK]

Thanks, I will give it a try.
--
CK

Hi,

Not directly no but you can do it like this

=IF(COUNTIF(E7:N7,"p")>0,SUMPRODUCT((ISNUMBER(SEARCH("Hours",D11000)))*(O1:O1000)),"")

Mike