Excel Magic Trick

Download Excel Start File: people.highlin...
Download Excel Finished File: people.highlin...
Full Page With All File Links: people.highlin...
From a List of dates, see how to create a formula with the SUMPRODUCT and EOMONTH functions that count the dates in each month. Also see a formula that will work in earlier versions of Excel using the functions: SUMPRODUCT, DATE, YEAR, MONTH.
Formula for Excel 2007 or Excel 2003.

[@excelisfun]

I am glad that you like them!

[@AjLewis-xw9hn]

Thank you so much!!!! I tried 12 different ways of doing this based off of searches and several hours--this is the only one that worked. I am not well-versed in excel and not ONE of the other "help" articles I read mentioned properly formatting the date reference.

[@ddenmon932]

@ExcelIsFun Thanks I will give it a try, and by the way... I have been following your videos for a while now and I want to thank you for your generosity in teaching excel, and the enthusiasm you bring to your craft.

[@stenionet]

You have the best tutorials.
Thank you very much.

[@excelisfun]

Dear ChicagoBears1,
I am glad that the videos and workbooks are useful!
Do you live in Chicago? I lived in Chicago during the Ditka years - boy was that a fun time to be a Bears fan in Chicago (I also lived just a few blocks from Wrigley Field - that - was fun too).
--excelisfun

[@excelisfun]

You are welcome!

[@excelisfun]

Dear marizmendi1656,
The only thing I can think of is that maybe the commas between the arguments in functions are semi-colons instead?
--excelisfun

[@excelisfun]

Dear stenionet,
You are welcome!
--excelisfun

[@excelisfun]

If the dates are serial numbers and are in a range, try the MAX function.

[@excelisfun]

There is one problem in 2007 with SUMPRODUCT that I know of, but it has to do with double negative, and that is not part of this formula... Try this alternative formula:
=SUMPRODUCT(1*(TEXT($B$8:$B$24,"mmmyyy")=TEXT(E7,"mmmyyy")))
Maybe??

[@ddenmon932]

@ExcelIsFun Your recommendation worked out perfectly, Thanks again!

[@jagankyt]

i like the way you present these videos.. welcome :) hey.. :)

[@excelisfun]

Dear marizmendi1656,
I just tried it on an Excel 2003 computer and I got it to work. Is the formula entered correctly? What do you see in the cell when you enter the formula?
--excelisfun

[@excelisfun]

Dear marizmendi1656 ,
NAME error means there is a word in the formula that is not an Excel "Name" or a function. Can you try to type the formula in a new Excel 2003 workbook to see if it works?
--excelisfun

[@excelisfun]

Dear ChicagoBears1,
Happy Excelling in Chicago!
--excelisfun

[@PayPhoney]

Hi Mike,
This video almost shows me what I’d like to do! ...but I just can’t get something to work.
Do you know a trick on how to find the last date of each month for which data was entered? So instead of a sumprouct for each month, I just want to look up for something that is on the latest day on the list of each month. I’ll appreciate your tips and tricks on this!

[@novasport007]

Hi I was wondering if you could help me?... I'm not sure which formula would work for me if I have three criterias that need to be met which includes a date range and then I need to sum up a column that meets the the criterias? Many thanks

[@ddenmon932]

Mike I am using excel 2007. I was able to get this to work on 1 of 2 sheets. On the 2nd sheet, the formula is only picking up some of the dates in the range matching the criteria. Is there some type of caveat or limitation with this approach that could account for this irregularity? I have tried everything to figure this out.

[@excelisfun]

You are welcome!