#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOE
 
Posts: n/a
Default sumif criteria

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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Duke Carey
 
Posts: n/a
Default sumif criteria

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!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default sumif criteria

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I use the sumif if I have multiple criteria (i.e. greater . Kellyatisl Excel Worksheet Functions 3 June 4th 06 06:33 AM
Multiple SUMIF Criteria azazel Excel Worksheet Functions 3 November 10th 05 09:31 PM
SUMIF with Mutiple Ranges & Criteria PokerZan Excel Discussion (Misc queries) 5 August 4th 05 10:31 PM
"criteria" in a sumif refering to the value in another cell mark Excel Discussion (Misc queries) 1 January 31st 05 08:39 PM
Can I use a cell reference in the criteria for the sumif function. Number Cruncher Excel Worksheet Functions 2 November 4th 04 08:52 PM


All times are GMT +1. The time now is 04:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"