=SUM(TRIM(LEFT(B1:B200,4))=A211) gives #VALUE!

  • Thread starter Thread starter Larry Bickford
  • Start date Start date
L

Larry Bickford

Have looked through more than 900 threads to find anything
similar to this, but didn't. (You guys did answer another
question of mine, using SUMPRODUCT, so thanks already!)

In B1:B200, I have data (exported from Access) of the
form "#.# Text", e.g.,
1.1 Alpha
1.3 Gamma
1.3 Gamma << yes, the leading space is deliberate
1.2 Beta
....

In cells A211:A213 I have
1.1, 1.2, 1.3
In B211:B213, I want the tally of how many from B1:B200
start with the corresponding value from A211:A213.
The formula
=SUM(TRIM(LEFT(B1:B200,4))=A211)
results in #VALUE!, even if I use Ctl+Shift+Enter
Other tries, such as COUNTIF(TRIM(LEFT(B1:B200,4)),=A211)
get an Error Dialog.

If I use the central part of the formula
TRIM(LEFT(B1,4))
in C1 (and copy it down to C200, I get the expected
results: 1.1, 1.3, 1.3, 1.2, ...), so that much works.

And I have seen some solutions using unary --;
what does that do, and when is it proper to use it?
 
Thanks, Frank. It works.
Now could someone explain these to me (so I can help
others):
1. What does the "--" do? Can't find any explanation of it
in Excel help, but I see it in many solutions here.
2. Why do I need the extra () around TRIM(...)=A211 ?

-----Original Message-----
try
=SUMPRODUCT(--(TRIM(LEFT($B$1:$B$200,4))=A211))

Regards
Frank Kabel
Frankfurt, Germany
 
Back
Top