SUMIF Formula where Excel is treating the Date criteria as Text

[DaveMitch]

Hi, I have a very simple SUMIF formula =sumif(L:L,O6,H:H) Where L:L are week commencing dates and O6 is the week commencing date I want summed from numbers in column H:H. As I'm building the formula, I can see it is treating the dates as a number, 43464.

The result should be 22, but I keep getting 0, I am presuming it is because of this issue.

I have ensured that the formatting of the dates is the same & looked through forums to try to understand how to rectify this, but I'm lost - if this is the problem.

Thanks in advance.

 

[RoryA]

I'd say the more likely cause is that either the numbers in column H are actually text, or the dates really don't match (eg if there's a time portion in one or other).

 

[DaveMitch]

Hi Rory,

Thank you for your reply. The more I try to learn the less I seem to know!

The dates in L:L are actually a formula, G1-WEEKDAY(G1)+1, where G1 is a manually inputted date, so I cannot see if, as per your suggestion, if there is time etc. being added to cause the conflict in formatting, or is the formula causing the conflict?
Dave

 

[RoryA]

That would make me think that my first suggestion is the problem. What does:

=countif(L:L,O6)

return?

 

[DaveMitch]

22!

 

[RoryA]

That does tend to confirm that the numbers in H are not numbers. What does:

=SUM(H:H)

return? 0?

 

[DaveMitch]

Hi Rory,

God, I've just spotted my error - what a dipstick...in H, I have another formula to simply put enter a 1 for me to finally work out %, I had written: IF(G1>0,"1","") meaning I was entering the 1 as text even though it was formatted as a number...gah...corrected & now the SUMIF works!

Thank you for your time in replying & helping me to work through & find the error!

 

[RoryA]

Glad to help.