Help please with formula

[Joe Gieder]

I am trying to use this fomula:
=SUM(IF('Priced BOM'!$H$3:$H$203<>"Yes",IF(LEFT('Priced
BOM'!$M$3:$M$203,16<>"Purchase History"),IF('Priced BOM'!
$W$3:$W$203<>"USAInfo",'Priced BOM'!$T$3:$T$203,0),0)))
to help sum different information but it gives me a
#value error. The problem lies in the portion IF(LEFT
('Priced BOM'!$M$3:$M$203,16<>"Purchase History") but I
need to use left (or another way) since sometinmes after
the wors Purchase History there are other pieces of
information I need. Is there a way to rewrite this
formula?

TIA
Joe

 

[Frank Kabel]

Hi
if this is your formula you'll have at least a missing bracket after
',16'

Try(not array entered):
=SUM(IF('Priced BOM'!$H$3:$H$203<>"Yes",IF(LEFT('Priced
BOM'!$M$3:$M$203,16<>"Purchase History"),IF('Priced BOM'!
$W$3:$W$203<>"USAInfo",'Priced BOM'!$T$3:$T$203,0),0)))

=SUMPRODUCT(--('Priced BOM'!$H$3:$H$203<>"Yes"),--(LEFT('Priced
BOM'!$M$3:$M$203,16)<>"Purchase History"),--('Priced
BOM'!$W$3:$W$203<>"USAInfo"),'Priced BOM'!$T$3:$T$203)

 

[Joe Gieder]

Thanks Frank.
Another set of eyes and your expertise helps greatly.

Joe

 

[Guest]

Frank,

-----Original Message-----
Hi
if this is your formula you'll have at least a missing bracket after
',16'

Try(not array entered):
=SUM(IF('Priced BOM'!$H$3:$H$203<>"Yes",IF(LEFT('Priced
BOM'!$M$3:$M$203,16<>"Purchase History"),IF('Priced BOM'!
$W$3:$W$203<>"USAInfo",'Priced BOM'!$T$3:$T$203,0),0)))

=SUMPRODUCT(--('Priced BOM'!$H$3:$H$203<>"Yes"),--(LEFT ('Priced
BOM'!$M$3:$M$203,16)<>"Purchase History"),--('Priced
BOM'!$W$3:$W$203<>"USAInfo"),'Priced BOM'!$T$3:$T$203)


--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:43d801c49037$2c33cd90 [email protected]...

.

 

[Guest]

Frank,
What would the difference be with using Sumproduct
instead of Sumif?

Thanks
Joe

 

[Frank Kabel]

Hi
SUMPRODUCT should be a little bit faster than the array entered SUM(IF
formula