sumif criteria

[DOE]

Using Excel 2000, I developed a grid incorporating +, -, words and values.
The abbreviated grid looks like this:
B C
3 Average 1
4 + 2
5 + 3
6 - 4
7 + 5
8 + 6
9 + 7
10 Average 8
11 - 9
12 - 10
13 - 11
14 + 12
15 - 13
16 - 14
17 Average 15

In cell B19, I set up the following formula: =SUMIF(B3:B17,"+",C3:C17).
Instead of returning the expected result of 35, the value was 12 (value in
B14). I even tried replacing all of the +'s and -'s (using letters) but the
formual result was 0 instead of 35. What went wrong? Thanks for any help!

[Duke Carey]

See if this works. It strips out the impact of spaces

=SUMPRODUCT(--(TRIM(B3:B17)="+"),C3:C17)


"DOE" wrote:

> Using Excel 2000, I developed a grid incorporating +, -, words and values.
> The abbreviated grid looks like this:
> B C
> 3 Average 1
> 4 + 2
> 5 + 3
> 6 - 4
> 7 + 5
> 8 + 6
> 9 + 7
> 10 Average 8
> 11 - 9
> 12 - 10
> 13 - 11
> 14 + 12
> 15 - 13
> 16 - 14
> 17 Average 15
>
> In cell B19, I set up the following formula: =SUMIF(B3:B17,"+",C3:C17).
> Instead of returning the expected result of 35, the value was 12 (value in
> B14). I even tried replacing all of the +'s and -'s (using letters) but the
> formual result was 0 instead of 35. What went wrong? Thanks for any help!

[Dave Peterson]

Could it be that the values in column C aren't really numbers.

If you format the column as General, then retype a few entries, does your
formula evaluate better?

If it does, you could continue to fix them manually or use a macro from David
McRitchie:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

DOE wrote:
>
> Using Excel 2000, I developed a grid incorporating +, -, words and values.
> The abbreviated grid looks like this:
> B C
> 3 Average 1
> 4 + 2
> 5 + 3
> 6 - 4
> 7 + 5
> 8 + 6
> 9 + 7
> 10 Average 8
> 11 - 9
> 12 - 10
> 13 - 11
> 14 + 12
> 15 - 13
> 16 - 14
> 17 Average 15
>
> In cell B19, I set up the following formula: =SUMIF(B3:B17,"+",C3:C17).
> Instead of returning the expected result of 35, the value was 12 (value in
> B14). I even tried replacing all of the +'s and -'s (using letters) but the
> formual result was 0 instead of 35. What went wrong? Thanks for any help!

--

Dave Peterson