Re: Caculating Columns Between Certain Dates


"Dermot" <Dermot@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C480935D-D83F-4BE1-9FEA-1148252AEB0B@xxxxxxxxxxxxxxxx
> Hi Bob,
> I tried entering the example formula but I get an error message.
> I was prompted to use the formula construction below
>
> SUMPRODUCT(Array 1,[Array2],[Array3],...)
> so I entered
> =SUMPRODUCT(A2:A200>= --"2005-01-01",[A2:A200,<=
> "2005-03-31"],[B2:B200="company_name"],[C2:C200+D2:D200])


The format you are seeing is the standard SUMPRODUCT formula, what I gave
you is a variation you will not see documented in the help files. The page I
gave in the last post gives a good explanation.

It is important that you neter the formula as supplied, if you muck about
with it, don't be surprised if it doesn't work. The only thing you should
change are the real cell ranges, and the test values, company name & dates.
The formula should be

=SUMPRODUCT(A2:A200>= --"2005-01-01",(A2:A200,<=--"2005-03-31"),(B2:B200="co
mpany_name"),(C2:C200+D2:D200))

When you see [...] in help, it doesn't mean that you use the square
brackets, it means that those parameters are optional.

> I wasn't too sure about the dashes you have included --"2005-01-01"
> Can you tell me what they do?


I use the -- to coerce the text date string into an Excel date that the
formula can work with. Some people use DATE(2005,01,01), that is just my
preference, as it is slicker and uses an ISO standard date format, as I am
UK based we suffer with Excel dates, so an ISO standard helps to make our
life easier.


.

Relevant Pages

  • Re: xlExcel9795 Not Supported in 2007? 2003?
    ... I attempted to use the xlExcel9795 format because I am looking for a file ... are not well documented in the help files online or otherwise. ... Jon Peltier, Microsoft Excel MVP ... format the macro reveals the use of the constant xlExcel8 instead. ...
    (microsoft.public.excel.programming)
  • Re: CDate problem
    ... There's an ISO standard date format that was designed for ... If you save your dates in ISO 8601 format then you'll ... Dim strName as string ...
    (microsoft.public.vb.general.discussion)
  • Date Format
    ... I wish to include the date in the footer of my PowerPoint slides, ... the ISO standard yyyy-mm-dd, which is the only unambiguous format (and ...
    (microsoft.public.mac.office.powerpoint)
  • Re: Broken iWork 09 FACT
    ... XML content. ... So was the SXW format used by ... ODT, etc., were becoming an ISO standard, they created DOCX to confuse ...
    (comp.sys.mac.advocacy)
  • Re: How to open Micrsofts so-called Open XML Format
    ... Microsoft Office Open XML Format Spreadsheet format. ... ISO standard document, claiming is is an "open" standard. ...
    (Ubuntu)