Help with SumIf

[Guest]

I am new to using formulas in Excel and need some help with using SumIf and a
date.
In column A I have the month and Year "Nov-06". I would like to do is have
Column C Sum only if the current day is < 12 of the month.
Thanks

 

[Guest]

This is the formula I have but I am getting 0 as a result.
Thanks

 

[Biff]

Try this:

=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(DAY(A1:A10)<12),C1:C10)

Biff

 

[Guest]

=SUMIF(A1:A15,"<11/7/2006",C1:C15)

 

[Biff]

Hold on there a second.......

In column A I have the month and Year "Nov-06".

If you have dates like Nov-06 how do you know what day of the month that is?

If I type in Nov-06 Excel evaluates it as Nov 6 2006 not Nov 2006

Seems to me that you should use the full date.

Biff

 

[Sandy Mann]

If I type in Nov-06 Excel evaluates it as Nov 6 2006 not Nov 2006

I assume that it is a typo because my XL97 evaluates Nov-06 to November 1
2006 not the 6th

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Biff]

No typo.

If I format a cell as mmm-yy and type in Nov-06 Excel (2002) shows it in the
formula bar as 11/6/2006.

If I don't preformat the cell and type in Nov-06 Excel automatically
converts it to 6-Nov and shows 11/6/2006 in the formula bar.

Biff

 

[Sandy Mann]

No typo.

mmmmmm......

It must just be me being the poor cousin again then <g>

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Biff]

Nah, just that the post isn't as clear as it could be.

Biff

 

[Roger Govier]

Sandy

XL2003
If I type Nov-06 into a standard cell or a cell preformatted as mmm-yy I
also see
01/11/2006

What a difference a stretch of water can make <vbg>

 

[Pete_UK]

Hi Roger, Sandy, Biff,

just to complete the range, with XL 2000 I also get 01/11/2006 when I
enter Nov-06. Must be something to do with Regional Settings.

Pete

 

[Sandy Mann]

I got called in to work at 2am this morning even although I'm supposed to be
on holiday, (it's 4:15 am here now), and so I took the opportunity to test
it out on out companies Excel 2002 SP3 and I again got 1/11/06, (11/1/06 to
you). Perhaps there is a difference between the British and the American
versions.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Keep going lads, we've got him outnumbered. <g>

--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Biff]

Screencap:

http://img294.imageshack.us/img294/6008/dateformatnu3.jpg

Biff

 

[Sandy Mann]

Hi Biff,

I never doubted you Biff. I tried changing the Regional Settings to English
(United States) and got the same as you.

I usually don't like changing the Regional Settings because as my mother
told me sixty years ago about pulling faces, "If the wind changes you'll be
stuck like that!" and although never believed her even then; you never know.
<g>
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Pete_UK]

I think what is happening is that in the UK we would enter dates as
dd/mm/yy, so if you enter 5/11 in a cell then Excel assumes this is a
date and will assume that the year is missing and thus take it as being
05/11/2006.

In the US and Canada, however, dates are normally entered as mm/dd/yy,
so if you enter Nov-06 Excel will again assume the year is missing and
treat this as 06/11/2006 (or 11/06/2006). Perhaps Biff can test this
out by entering Nov-05 in a cell pre-set to mmm-yy - if my reasoning is
correct this should display as Nov-06 but the underlying value will be
5th Nov 2006.

I also don't like changing the Regional Settings <bg>

Hope this helps.

Pete

 

[Roger Govier]

Good reasoning, Pete and my bet would be on you being correct.

 

[Biff]

Perhaps Biff can test this

out by entering Nov-05 in a cell pre-set to mmm-yy - if my reasoning is
correct this should display as Nov-06 but the underlying value will be 5th
Nov 2006.

Your reasoning is correct (as usual!)

Biff

 

[Pete_UK]

Thanks for confirming this, Biff. (Puts my mind at rest on that little
issue !)

Pete