Sumif Fails w Date as Text

[expect_ed]

I have a column (A) that calculates a date to the nearest 1st of the month
based on a date in another column on another sheet (Sheet2!G).
The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1"
Column A is formatted as text.
Then I have a set of cells (P1 to P8) that calculates the first of the month
out 2 to 10 months.
That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies
from 2 to 10.
So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get
similar values. Column P is also formatted as text.
In Column C there are work hours associated with the month starts in col A
and I want to total the work hours for each month start, so in Column H If
have the following calculations:

H1: =SUMIF(A10:A100,P1,C10:C100)
H2: =SUMIF(A10:A100,P2,C10:C100)
etc.

So when P2 = 10/1 I would expect H2 to contain the total of any cells in C
that have a value of 10/1. Instead I get a zero.

I have tried to confirm that the values match with the formula =IF(P2 =
A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells
match, but I still get only a 0 in H2.

Any help appreciated.
thanks
ed

 

[Toppers]

How is Column C formatted?

 

[expect_ed]

Col C is formatted as a number, with 2 decimal places.

 

[Roger Govier]

Hi Ed

I think you have the formula the wrong way round.
You are saying the "dates" are in C and the values to be summed in A.
Try
=SUMIF(C10:C100,P1,A10:A100)

 

[expect_ed]

I don't think that is correct. According to Excel the first parameter is the
compare range and the 3rd parameter is the cells to be summed.
In any case I tried switching it and still get zero.
Thanks anyway.
ed

 

[Roger Govier]

Yes, I misread your posting.
Your dates are in A and the values to be summed are in C.
It works absolutely fine for me. There must be something wrong with your
data.
Perhaps the values in column C are text, not numeric. That would give a
result of 0.
Try testing columns C with =ISNUMBER(C1) and copying down.

 

[expect_ed]

Yes, that seems to be the problem. Even when I set the format to numeric the
cells remain as non-numeric. If I try =value(c10) I get a #Value error.

Any suggestions for forcing the cells to numbers???
thanks for your help.
ed

 

[Roger Govier]

Hi I
in a blank cell on your sheet enter 1
Copy that cell
Mark the range of your "non numeric data"
Paste Special>Multiply

 

[expect_ed]

Well, I actually simplified that col a bit to shorten the description.
The cells in row C actually contain a formula:
=IF(Sheet2!M37="",Sheet2!L37,Sheet2!M37)
because I am pasting a worksheet into Sheet 2 that will have work hr values
either in col L (original) or col M (updated).

So when I use the paste special multiply everything changes to #VALUE errors??

The pasted cells in sheet2 come in as General formatted.

Thanks again for your help.
ed

 

[Roger Govier]

Hi

Maybe if you change that formula to
=IF(Sheet2!M37="",--(Sheet2!L37),--(Sheet2!M37))

Set the format of the receiving ells to hh:mm
Set the format of the cell with the Sumif formula to [hh]:mm

 

[expect_ed]

As soon as I change the formula, I get a #VALUE error.
If it makes any difference, the information I am pasting into Sheet2 is a
web page.
The numeric values are in what come in as merged cells that are left
justified and when checked for format as general.
Attempting to convert them using =Value() also results in a #VALUE error.
I'm stuck,
Thanks for your help.
ed

Hi

Maybe if you change that formula to
=IF(Sheet2!M37="",--(Sheet2!L37),--(Sheet2!M37))

Set the format of the receiving ells to hh:mm
Set the format of the cell with the Sumif formula to [hh]:mm
--
Regards
Roger Govier

Well, I actually simplified that col a bit to shorten the description.
The cells in row C actually contain a formula:
=IF(Sheet2!M37="",Sheet2!L37,Sheet2!M37)
because I am pasting a worksheet into Sheet 2 that will have work hr
values
either in col L (original) or col M (updated).

So when I use the paste special multiply everything changes to #VALUE
errors??

The pasted cells in sheet2 come in as General formatted.

Thanks again for your help.
ed

 

[Roger Govier]

Hi

there may be non-breaking spaces in the data that is imported.
In another column, enter
=SUBSTITUTE(SUBSTITUTE(C1," ",""),CHAR(160),"")
Try using that as the column of data in your SUMIF() formula

--
Regards
Roger Govier

As soon as I change the formula, I get a #VALUE error.
If it makes any difference, the information I am pasting into Sheet2 is a
web page.
The numeric values are in what come in as merged cells that are left
justified and when checked for format as general.
Attempting to convert them using =Value() also results in a #VALUE error.
I'm stuck,
Thanks for your help.
ed

Hi

Maybe if you change that formula to
=IF(Sheet2!M37="",--(Sheet2!L37),--(Sheet2!M37))

Set the format of the receiving ells to hh:mm
Set the format of the cell with the Sumif formula to [hh]:mm
--
Regards
Roger Govier

Well, I actually simplified that col a bit to shorten the description.
The cells in row C actually contain a formula:
=IF(Sheet2!M37="",Sheet2!L37,Sheet2!M37)
because I am pasting a worksheet into Sheet 2 that will have work hr
values
either in col L (original) or col M (updated).

So when I use the paste special multiply everything changes to #VALUE
errors??

The pasted cells in sheet2 come in as General formatted.

Thanks again for your help.
ed

:

Hi I
in a blank cell on your sheet enter 1
Copy that cell
Mark the range of your "non numeric data"
Paste Special>Multiply

 

[expect_ed]

Roger,
Thanks so much for your persistence. It finally paid off. It must have
been the non-breaking spaces. That formula allowed me to get to a working
solution. Thanks so much for your help.

I'd never heard of non-breaking spaces. Is there a reference anywhere you
know of that would provide more info?

Thanks again for you help.
ed

 

[Roger Govier]

Hi Ed

The Non Breaking Space (nbsp) is encountered a lot when you import data from
the Web.
Here is a reference that will give you a little more information.
http://en.wikipedia.org/wiki/Non-breaking_space