Proper SUMIF Technique

kcoon

New Member
Joined
Nov 22, 2005
Messages
3
Does anyone here know why the following formula gives me 0 results (in this formula, the text "Dave" appears in column E):

=SUMIF(C12:E31,"Dave",C12:C31)

Yet, when I move the colum I am evaluating to the left of the sum range I get "55" (now the text is in Column B" -- I just copied and pasted it)

=SUMIF(B12:D31,"Dave",C12:C31)

Can SUMIF only evaluate data to the left of the sum range?
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello kcoon

=SUMIF(C12:E31,"Dave",C12:C31)
SUMIF(Criteria_range,criteria,sum_range)

You've got the column "Dave" will be found in Overlapping with the column to sum :confused: :wink:

What Column is the Name In ?
What Column are the numbers in ?


=SUMIF(E:E,"dave",C:C)
In this example the values to sum are in Column C and the name Dave can be found in Column E .
 

kcoon

New Member
Joined
Nov 22, 2005
Messages
3
Nimrod, I thought I followed your comment about overlapping, but when I don't include Column C in the range I get some funky result (I think it tries to sum Column D).

This is what basically what it looks like now:

Column B Column C Column D Column E

Dave 4 $10,000 Dave
Charlie 5 $11,000 Charlie
Frank 6 $10,500 Frank
Dave 7 $9,000 Dave

I'm looking for sum = 11 without using column B (if that's possible)
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
=SUMIF(E:E,"dave",C:C)
In this example the values to sum are in Column C and the name Dave can be found in Column E .
 

kcoon

New Member
Joined
Nov 22, 2005
Messages
3
That definitely did the trick. Thanks.

Still, I'm slightly perplexed as to why I can specify a range of multiple columns in one instance, =SUMIF(B:D,"Dave",C:C), and not in the other. Is there some basic concept that I'm missing?
 

Forum statistics

Threads
1,078,474
Messages
5,340,546
Members
399,383
Latest member
rahmanab001

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top