Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
Multiple SUMIF Criteria | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
"criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |