formula that works in a different workbook

[Guest]

Hello folks I have a formula here that works in another work book just fine
the only difference in these two formulas is the column that they reference
and these {} symbols when I click on the cell that they represent. here is
the formula.
=SUM(IF('WELD LOG'!$H$8:$H$2043=C15,IF('WELD
LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$I$8:$I$2043=C15,IF('WELD
LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$J$8:$J$2043=C15,IF('WELD
LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$K$8:$K$2043=C15,IF('WELD
LOG'!$F$8:$F$2043="SW",1,0)))
also where the "SW" is substitute that with "WOL"
also in about 5 cells the #value shows up and the rest of the cells just
show up 0.
Like I said in the other workbook this formula works fine. I would
appreciate any help as I have pretty much exhausted myself trying to figure
this one out.

 

[bridgesmj]

Sounds like an array formula.

Enter the formula into the cell you want, having made the edits you
said below. Press Contol+Shift+Enter (CSE).

Should work.

 

[Bob Phillips]

Not sure what the problem is but the {} suggest the formula is entered as an
array formula.

Try this non-array version

=SUMPRODUCT(--('WELD LOG'!$H$8:$H$2043=C15),--('WELD
LOG'!$F$8:$F$2043="SW"))+
SUMPRODUCT(--('WELD LOG'!$I$8:$I$2043=C15),--('WELD
LOG'!$F$8:$F$2043="SW"))+
SUMPRODUCT(--('WELD LOG'!$J$8:$J$2043=C15),--('WELD
LOG'!$F$8:$F$2043="SW"))+
SUMPRODUCT(--('WELD LOG'!$K$8:$K$2043=C15),--('WELD
LOG'!$F$8:$F$2043="SW"))

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

or even

=SUMPRODUCT((('WELD LOG'!$H$8:$H$2043=C15)+('WELD
LOG'!$I$8:$I$2043=C15)+('WELD LOG'!$J$8:$J$2043=C15)+('WELD
LOG'!$K$8:$K$2043=C15))*
('WELD LOG'!$F$8:$F$2043="SW"))

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Not sure what the problem is but the {} suggest the formula is entered as an
array formula.

Try this non-array version

=SUMPRODUCT(--('WELD LOG'!$H$8:$H$2043=C15),--('WELD
LOG'!$F$8:$F$2043="SW"))+
SUMPRODUCT(--('WELD LOG'!$I$8:$I$2043=C15),--('WELD
LOG'!$F$8:$F$2043="SW"))+
SUMPRODUCT(--('WELD LOG'!$J$8:$J$2043=C15),--('WELD
LOG'!$F$8:$F$2043="SW"))+
SUMPRODUCT(--('WELD LOG'!$K$8:$K$2043=C15),--('WELD
LOG'!$F$8:$F$2043="SW"))

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Well what do ya know it worked. All the headache for Control+Shift+Enter.
Guess thats what I get for not finishing my video professor. HAHA
--
Jude Reason
CWI #06041451

Sounds like an array formula.

Enter the formula into the cell you want, having made the edits you
said below. Press Contol+Shift+Enter (CSE).

Should work.