Formula not displaing results just dollar signs

moe10134

Board Regular
Joined
Sep 8, 2005
Messages
157
I have a SUMIF formula created to sum hours that have the word Monday with the total for that day. The formula is not calculating any results for that total. Is there another formula I could try? Is this a worksheet issue? Protect/Not Protected? MS Excel 2003 vs MS Excel 2007? Why is the formula not erroring out? Also, if copied from a working spreadsheet into another speadsheet no data populates. This formula was created in the MS Excel 2007 version.

=SUMIF($H$39:$H$61,"Monday",$R$39:$R$61)

Any suggestions?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
My guess would be the values in H39:H61 are Dates, formatted to show weekday mmmm.

Try this

=SUMPRODUCT(--(WEEKDAY($H$39:$H$61)=2),$R$39:$R$61)

1 = Sunday
2 = Monday
3 = Tuesday
Etc..

Hope that helps.
 

moe10134

Board Regular
Joined
Sep 8, 2005
Messages
157
Thank you for the suggestion. Didn't work, though. Still came back with a 0 result. I don't understand what I'm doing wrong. Both of those formulas should have worked??
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Another possiblility is the numbers in R39:R61 are not numbers at all..but "Numbers Stored As Text"

What does this formula returnd

=ISNUMBER(R39)
And fill it down to R61

Are they all True, all False, or a mixture?
 

moe10134

Board Regular
Joined
Sep 8, 2005
Messages
157

ADVERTISEMENT

=ISNUMBER(R39) came back TRUE. It's a dollar amount.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
do the same thing on H39

=ISNUMBER(H39)


Also, you didn't quite answer the question...
Do they ALL return True, as in R39 and R40 and R41 etc...
 

moe10134

Board Regular
Joined
Sep 8, 2005
Messages
157

ADVERTISEMENT

R23 = Day = "Monday" = ISTEXT = TRUE
K39 = Hours = "6" = ISNUMBER = TRUE
R39 = Dollar Amount = "$350" = ISNUMBER = TRUE

=SUMIF($K$39:$K$61,R23,$R$39:$R$61)

Formula is just not populating as it should. Not sure why when my original formula worked at one point, now it is not working. All other formulas in the spreadsheet are calculating fine. Not sure why this one is not??
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
There's the problem...

if R23 is a TEXT value of "Monday"
And The values in K39:K61 are NUMBERS, like 6
Then no value in K39:K61 will = "Monday"
 

moe10134

Board Regular
Joined
Sep 8, 2005
Messages
157
Auh, Good point! I added range I39:K61 and it worked. I kept telling it to reference "Monday" but did not include that in the lookup range. It's working fine now.

Thanks for all your help!! =)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,816
Messages
5,513,555
Members
408,958
Latest member
KDox

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top