counting characters and sumif

[Becky Lindholm]

LYNA-Z $412,458.72
SBC $54,411.90
BP $55,971.50
NWL $52,095.00
LNT $58,260.00
MEL $62,021.80
XOM $65,446.20
USB $58,968.00
MMM $96,836.54

I need to write a formula that will see how many characters are in each cell of the first column. If there are 1, 2 or 3 characters it will sum the corresponding value. If there are more than three characters I don't want the corresponding value included in the sum. I would appreciate any assistance. Thanks!

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(LEN(A1:A9)<4),--(LEN(A1:A9)>0),B1:B9)

 

[Aladin Akyurek]

LYNA-Z $412,458.72
SBC $54,411.90
BP $55,971.50
NWL $52,095.00
LNT $58,260.00
MEL $62,021.80
XOM $65,446.20
USB $58,968.00
MMM $96,836.54

I need to write a formula that will see how many characters are in each cell of the first column. If there are 1, 2 or 3 characters it will sum the corresponding value. If there are more than three characters I don't want the corresponding value included in the sum. I would appreciate any assistance. Thanks!

1.

=SUM(SUMIF(A2:A10,{"?","??","???"},B2:B10))

2.

=SUMPRODUCT(--(A2:A10<>""),--(LEN(A2:A10)<=3),B2:B10)