SUMIF giving me 0

G

Gerrie

I have hours in one column. I have "Y" in second column. I want to total
the hours that only have Y in second column. I find if I switch the columns,
putting Y in the first column & hours in the second column, it works. I
can't switch them though because other people use this spreadsheet & need to
use it as is. Is there a way around this problem?
 
F

FSt1

hi
you might have your sum range and criteria range reversed in the formula.
post your fomula and columns letters of the times and "Y".

Regards
FSt1
 
P

Per Jessen

With hours in column A and Y in column B, this formula should do it:


=SUMIF(B1:B13,"Y",A1:A13)

Regards,
Per
 
G

Gerrie

It doesn't work unless I switch the hours column with the Y column. Then it
works. I would think this would work whichever side either column is on.
what am I doing wrong?
 
F

Fred Smith

What you are doing wrong is thinking that Excel can guess the "if" column
and the "sum" column. It can't (or more correctly, doesn't). The "if" column
is first, the "sum" column is last. You can't interchange them.

Regards,
Fred.
 
P

Per Jessen

The problem may be in your formula. It should look like this:

SUMIF(Cells to test, Condition, Cells to sum)

Post your formula for further help.

Regards,
Per
 
G

Gerrie

Don't know how, but I'll try this:

Column R lists 8 in each cell in four rows
Column S lists y in each cell in four rows (same rows as R column)

Formula is:
SUMIF(R816:S820, "y" ,R816:R820)

I get zero and want to get 32. If I switch data and put y in the R column
and 8 in the S column, and show formula reversed, I get the total
 
F

Fred Smith

Your first range is incorrect. It contains two columns rather than one. Try
it this way:
=SUMIF(S816:S820, "y" ,R816:R820)

Regards,
Fred.


Gerrie said:
Don't know how, but I'll try this:

Column R lists 8 in each cell in four rows
Column S lists y in each cell in four rows (same rows as R column)

Formula is:
SUMIF(R816:S820, "y" ,R816:R820)

I get zero and want to get 32. If I switch data and put y in the R column
and 8 in the S column, and show formula reversed, I get the total
Click to expand...
 
G

Gerrie

Well, when you gave me a response all was well. Today I just decided to
check one of the files where I'm using this SUMIF formula. The SUMIF formula
calculates to 10 hrs. and manually by me to 12.5 hours. I've redone the
formula twice, but it's still calculating wrong. What could be the problem?
 
G

Gerrie

I just went back to the spreadsheet and I re-entered the last few hours again
and then the calculation worked! I don't understand why I would have had to
enter the data again. That's a mystery.
 
Top