=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:
Dude. I need to keep those notes for myself.
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. :)
Post a Comment