Re: Sumproduct Date clarification

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

That's a good point, Bob.

I myself would not use that expression in a formula. I just don't like the
way it looks! I would use a cell to hold the date or use the Date function
in the formula.

I was just showing the OP how it could be done using the date string.

Biff

"Bob Phillips" <bob.phillips@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:O%23NDs46XGHA.1192@xxxxxxxxxxxxxxxxxxxxxxx
Mr Biff,

Love to see you coercing a date with the unary operator, but could I
suggest
that you use the form

=SUMPRODUCT(((register01532!$b$7:$b$401>=--"2006-01-01")

it removes all ambiguites with dates re mm/dd or dd/mm formats.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Biff" <biffinpitt@xxxxxxxxxxx> wrote in message
news:OWxVwA3XGHA.4184@xxxxxxxxxxxxxxxxxxxxxxx
Hi!

Question is: Why did I have to use the Date function in one formula and
not
the other?

You didn't have to use the Date function. In the one formula you're
referencing a cell that holds the date.

In the below formula:

=sumproduct(((register01532!$b$7:$b$401>=1/1/06)...

You have what *YOU* think is a date but Excel sees 1 divided by 1 divided
by
6.

Try it this way:

=sumproduct(((register01532!$b$7:$b$401>=--"1/1/2006")

Biff

"Walter Mayes" <wmayes@xxxxxxxxxxxx> wrote in message
news:d6037$443efc8e$943fe3e4$23864@xxxxxxxxxxxxxxx
In one of my spreadsheets I have a sumproduct formula that looks
down
a
column of dates and returns 7 day totals for certain items. Works
great.
The formula is, in part:
=sumproduct(((meter readings!a$4:$a$1064>p$4).........

Column A contains =b4. Both columns, A and B, are formatted as dates:
I.E.
04/03/06 P4 is also formatted as a date I.E. 3-Apr-06

In another spreadsheet I was setting up a formula to get quarterly
totals. Same basic idea as the above formula. My formula is/was, in
part:

=sumproduct(((register01532!$b$7:$b$401>=1/1/06)...

Column B formatted as dates. This would not work. Returned nothing but
0's.
By modifying the formula to .....>=date(2006,1,1)).... it worked.

Question is: Why did I have to use the Date function in one formula
and
not the other?

Walter Mayes









.

Relevant Pages

  • Re: Relative Addresses in equations
    ... > "Bob Phillips" wrote in message ... >> (remove nothere from the email address if mailing direct) ... >>>> all the trouble time for the given job type, ... >>>> in the corresponding cell in the previous table. ...
    (microsoft.public.excel.programming)
  • Re: Caculating Columns Between Certain Dates
    ... (remove nothere from the email address if mailing direct) ... >>> Thanks for the reply Bob, ... >>> I have looked up the SUMPRODUCT Function in my excel inside out book ... >>> to be a specific cell to ensure the correct cell references? ...
    (microsoft.public.excel.newusers)
  • Re: Caculating Columns Between Certain Dates
    ... > I missed this posting and posted a lot of rubbish in the two postings at ... >> Secondly, in the formula, when referring to a date cell you don't need ... >> (remove nothere from the email address if mailing direct) ... >>> Hi Bob. ...
    (microsoft.public.excel.newusers)
  • Re: Array index, match problem
    ... I have my app running now, ... the data in the "J19 input cell" is being ... "Bob Phillips" wrote: ... > (remove nothere from the email address if mailing direct) ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Announcing birthday
    ... << Bob Phillips]All you need is ... (remove nothere from the email address if mailing direct) ... Please note that the OP's condition is that there is a "Birthday" ... The months are different yet the birthday in Cell B1 is still within 10 ...
    (microsoft.public.excel)