# SUMIF formula returning a 0 value

#### Craig4670

##### Board Regular
Hello:
I have a question about using the SUMIF formula.

I have a column that I would like to have data summed based on the criteria selected by the user. The column that has that data, has formulas calculating a total to get the dollar amount. I'm not sure if the SUMIF function is not working correctly based on other formulas in the cells.

Heres what I have - shortened version:
 Cell B9 Name Cell L8 Cost Cell B10 Jim Cell L9 \$1500 Cell B11 Joe Cell L10 \$2000 Cell B12 Jim Cell L11 \$2500

<tbody>
</tbody>

In Cell B1000, I have the name Jim.
In Cell C1000, I would like the sum of all costs from column L associated with Jim to be totaled.
The formula I have is =SUMIF(B10:B12,"B1000",L9:L12)
It is returning a 0. I'm not sure if this is due to calculation formulas in column L.

How can I fix this to make it work? Any help would be appreciated.

Thanks!

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

##### MrExcel MVP
The formula should be:

=SUMIF(B10:B12,B1000,L9:L12)

#### Weazel

##### Well-known Member
try removing the quotation marks from the cell reference....

=SUMIF(B10:B12,"B1000",L9:L12) should be

=SUMIF(B10:B12,B1000,L9:L12)

Also the ranges don't match so that will probably throw off your total a bit

#### Craig4670

##### Board Regular
The formula should be:

=SUMIF(B10:B12,B1000,L9:L12)

Thank you for your response. The formula I am using is the same as what you said. I made a mistake in my original post. I put " " around B1000. The formula is still returning a 0 value.

#### Craig4670

##### Board Regular
try removing the quotation marks from the cell reference....

=SUMIF(B10:B12,"B1000",L9:L12) should be

=SUMIF(B10:B12,B1000,L9:L12)

Also the ranges don't match so that will probably throw off your total a bit

Here is a corrected table.
 Cell B8 Name Cell L8 Cost Cell B9 Jim Cell L9 \$1500 Cell B10 Joe Cell L10 \$2000 Cell B11 Jim Cell L11 \$2500

<tbody>
</tbody>
Sorry for the mistake. I am using the formula =SUMIF(B9:B11,B1000,L9:L11)
The formula is showing a 0 value.

#### Weazel

##### Well-known Member
are you sure the 'numbers' are numbers and not numbers formatted as text?

you can try putting a 1 in a blank cell somewhere, and copy it,
highlight the range of numbers, right click, paste special, tick multiply and click ok and see if that fixes it

#### Craig4670

##### Board Regular
are you sure the 'numbers' are numbers and not numbers formatted as text?

you can try putting a 1 in a blank cell somewhere, and copy it,
highlight the range of numbers, right click, paste special, tick multiply and click ok and see if that fixes it

I double checked the numbers - they are formatted as currencey. I tried your suggestion, but it still did not fix the problem.

#### Weazel

##### Well-known Member

=SUMPRODUCT((B9:B11=E11)*(L9:L11))

E11 is whats holding the criteria Jim or Joe, so adjust it to where you need it and see if that works

##### MrExcel MVP

=SUMPRODUCT((B9:B11=E11)*(L9:L11))

E11 is whats holding the criteria Jim or Joe, so adjust it to where you need it and see if that works

Or...

=SUMPRODUCT((B9:B11=E11)+0,SUBSTITUTE(L9:L11,"\$","")+0)

#### Craig4670

##### Board Regular

=SUMPRODUCT((B9:B11=E11)*(L9:L11))

E11 is whats holding the criteria Jim or Joe, so adjust it to where you need it and see if that works

I tried your suggestion and it still returned a 0 value. On the bottom of the spreadsheet the user should be able to type in the name and the summed total should be calculated. In Cell B1000, I have the name Jim.

Replies
2
Views
195
Replies
1
Views
87
Replies
2
Views
341
Replies
5
Views
211
Replies
1
Views
107

1,195,990
Messages
6,012,723
Members
441,722
Latest member
tpaman1975

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back