sumif problem Office 2007 and 2008

[nip]

have a little spreadsheet that is like an invoice. number of hours
times certain job at a certain rate... works well except in the bottom
i want to have the certain jobs have a total of the hours
It works well for all but one
=SUMIF(F12:F45,"Rev Hour",E12:E45)

It is driving me nuts can anyone help? Rev Hour is in a defined list
on sheet.

 

[T. Valko]

=SUMIF(F12:F45,"Rev Hour",E12:E45)

Your formula is looking for the text value "Rev Hour" in the range F12:F45.

What does this mean:

 

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(MATCH(F12:F45,RevHour,0))),E12:E45)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[nip]

Your formula is looking for the text value "Rev Hour" in the range F12:F45.

What does this mean:


--
Biff
Microsoft Excel MVP







- Show quoted text -

What is trying to be accomplished here is if in cells f 12 > 45 say
the word rev hour then count the number in cells e 12 > 45 and put
that number here.
Works for my purpose but this time will not work for this particular
word + scenerio... change the formula to match the text in one that
works and it works, change it back to the original and it provides a 0
answer.

 

[nip]

=SUMPRODUCT(--(ISNUMBER(MATCH(F12:F45,RevHour,0))),E12:E45)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)







- Show quoted text -

give a 0 for the answer
No dfference

 

[T. Valko]

Ok, then that tells me those cells that look like they contain "Rev Hour"
don't actually contain "Rev Hour".

There may be unseen characters in those cells like leading/trailing spaces.

See if this works:

=SUMPRODUCT(--(TRIM(F12:F45)="Rev Hour"),E12:E45)

--
Biff
Microsoft Excel MVP

Your formula is looking for the text value "Rev Hour" in the range
F12:F45.

What does this mean:


--
Biff
Microsoft Excel MVP







- Show quoted text -

What is trying to be accomplished here is if in cells f 12 > 45 say
the word rev hour then count the number in cells e 12 > 45 and put
that number here.
Works for my purpose but this time will not work for this particular
word + scenerio... change the formula to match the text in one that
works and it works, change it back to the original and it provides a 0
answer.

 

[Jim Rech]

Go to one of the "Rev Hour" entries and press F2. See if there is a space
at the end or something else that shouldn't be there.

--
Jim

=SUMPRODUCT(--(ISNUMBER(MATCH(F12:F45,RevHour,0))),E12:E45)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)







- Show quoted text -

give a 0 for the answer
No dfference

 

[nip]

Go to one of the "Rev Hour" entries and press F2.  See if there is a space
at the end or something else that shouldn't be there.

--




give a 0 for the answer
No dfference- Hide quoted text -

- Show quoted text -

Problem solved. there was a trailing space after the Hour... Thank
you and I also took out the space between the two words although I
have not had a problem with a space between words as they are enclosed
in the ""...