sumif problem Office 2007 and 2008

  • Thread starter Thread starter nip
  • Start date Start date
N

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.
 
=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:
 
=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)
 
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 -
Click to expand...

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.
 
=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 -
Click to expand...

give a 0 for the answer
No dfference
 
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 -
Click to expand...

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.
 
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 -
Click to expand...

give a 0 for the answer
No dfference
 
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 -
Click to expand...

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 ""...
 
Back
Top