Thursday, November 6, 2008

The bane of my day

=SUMPRODUCT((('TO COME'!A2:A286>=39722)-('TO COME'!A2:A286>39752))*(LEFT('TO COME'!B2:B286,3)="120"))

 

It took me two hours of work to come up with this equation for an excel project at work. It's days like to day that I really realize just how little I know about excel. :) Now I have to modify this 143 times to finish up the project...

 

Some notes for future self-reference:

- Excel stores dates as long digits (39722 = 10/01/2008, and 39752 = 10/31/2008 in the above)

- You can use =DATEVALUE("MM/DD/YYYY") to find these digits, important to use four digit year info, otherwise error occurs. Quotations are also very important

- Remember to change the formatting on any cells that use these long digits, otherwise they will just display the date

- You CANNOT put quotations around the date numbers in the final equation though, because then it returns an empty search

- For some reason when using the LEFT and RIGHT commands you MUST put quotations around the digits you are searching by

- No wildcards allowed in array multiplications so you must search filter by ISNUMBER, SEARCH, or LEFT/RIGHT commands depending on the variables

- COUNT command is useful for single variable filtering only. SUMPRODUCT is the most realistic for multiple variables

2 comments:

melissa said...

Dude. I need to keep those notes for myself.

TomboCheck said...

Melissa - And to think; this morning I only knew how to use the basic SUM command for a single worksheet! :) Today was definitely a learning experience, but now I'm a cross-sheet, multi-variable, auto-populating son of a gun!

The technotes that will be going into the folder with this file will be expansive. Knowing my luck somebody will accidentally delete the whole workbook not long after I get all of this finalized. :)