SUMIF formula returning a 0 value

[Craig4670]

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!

 

[Aladin Akyurek]

The formula should be:

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

 

[Weazel]

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]

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]

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]

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]

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]

ok, how about trying

=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

 

[Aladin Akyurek]

ok, how about trying

=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]

ok, how about trying

=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.