SUMIF formula

  • Thread starter Thread starter Jay Fastiggi
  • Start date Start date
J

Jay Fastiggi

I used the SUMIF formula to add up a bunch of numbers in a column that meet
a specific criteria (nothing complex). However, when I add the amounts up
manually I come up with a materially different total. Is there any reason
why the SUMIF formula would not work properly?

Thank you.
 
I used the SUMIF formula to add up a bunch of numbers in a column that meet
a specific criteria (nothing complex). However, when I add the amounts up
manually I come up with a materially different total. Is there any reason
why the SUMIF formula would not work properly?

Thank you.
Click to expand...

The most common explanations for that sort of issue:
1. Incorrect formula syntax in the SUMIF function.
2. Values that appear to be NUMERIC or DATE but are actually TEXT.


--ron
 
"New"
ISSUE
ALLOC


R I 1,038.58

125.72
(125.72)
14,658.94
551.69
580.24
1,132.29
9,143.59
4,933.46
12,465.06
970.17
2,998.83
6,511.95
3,120.97
4,930.68
1,766.85
2,870.04
8,947.60
2,351.44
1,043.35
2,285.90
3,365.69
546.98
546.98
609.10
6,300.96
3,540.01
773.65
1,228.24
4,571.79
3,868.25
21,879.01
2,545.22
1,463.26
0.00
3,868.25
3,143.88
2,465.34
800.06
14,205.92
2,170.59
0.00
R 420.86
R 585.76
R 576.54
R 415.09
R 259.44
R 23.94
R 1,383.64
R 69.18
R 1,012.68
R 5,024.31
R 75.81
R 276.73
R 720.67
R 50.33
R 13,802.95
R 74.71
R 1,937.09
R 1,464.43
R 933.72
R 326.31
R I 153.12
R I 198.00
R I 0.00
1,591.85
7,428.65
700.42
3,183.71
2,546.97
21,224.73
4,244.95
R 536.96
R 657.78
R 3,141.23
R 268.48
R 268.48
R 2,147.85
R 134.24
R 204.05
R 322.18
R 26.85
R 536.96
422.98
6,621.90
253.07
1,054.44
632.67
455.31
R 33.40
R 694.70
R 64.13
R 46.76
R 534.38
R 801.57
R 1,469.55
2,993.02
2,254.74
9,976.72
2,993.02
3,990.69
59,860.33
3,990.69
1,995.34
R 507.15
R 633.93
R 1,004.15
R 253.57
R 1,267.87
R 507.15
R 13.19
R 13.19
R 105.53
R 263.83
R 527.65
R 593.61
R 633.18
2,095.19
2,095.19
6,285.56
10,426.33
4,170.53
R 1,059.40
R 529.70
14,515.96
31,105.63
6,221.13
R 92.18
13,934.12
4,958.49
3,966.79
5,952.62
R 505.04
2,917.44
15,559.70
1,895.60
4,862.41
R 24.79
R 1,487.52


481,271.19
480,232.61
(18,728.81)

134,880.69
27%

52,736.08
11%



All I'm doing is summing anything that has an "R" next to it. Sumif formula
gives me 134,881, when I manually add it up I get 52,736.

Thanks.
 
the cells which begin with a non numerical value will be recognized a
text and not numerical and therefore will not be summed as you woul
have hoped

try moving the "R" to a different cell using a macro and then perfor
the function agai
 
You need to separate the letter "R" from the cell entries before you ca
do any mathematical operation. Assuming that the format is always "
xxxxx" then you can do this.

Enter in Cell B1 (and copy down until your range requirements are met)


=IF(ISTEXT(A1),LEFT(A1,FIND(\" \",A1)-1),\"\"

and in Cell C1 enter this formula

=IF(ISTEXT(A1),RIGHT(A1,LEN(A1)-SEARCH(\" \",A1)),A1

and copy down until your range requirements are met

Columns B and C are merely helper columns. In Cell D1, your formula t
add all the entries preceded by the letter "R" will then be

=SUMPRODUCT((B1:B100=\"R\")*(C1:C100)

There might be a faster and more efficient solution but in th
meantime, this will work for you.

Regards
 
Back
Top