CountIf by Month-to-date

A

ashley0578

I have a workbook that uses this function:

=COUNTIF('unfunded deals'!A:A,">="&TODAY()-30).

My only problem is that I actually need to count by month. How can I
rewrite this so that it will show me, for instance, Month-to-date?
 
B

Bob Phillips

maybe something like

=SUMPRODUCT(--(MONTH('unfunded deals'!A:A)=MONTH(TODAY())),--YEAR('unfunded
deals'!A:A)=YEAR(TODAY()))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

bpeltzer

You'd need to replace today()-30 with a calculation that returns the
beginning of the month: today()-day(today())+1.
If you want a specific month, I'd use two countif functions, along the lines
of
=COUNTIF('unfunded deals'!A:A,">="&date(2006,1,1)) - COUNTIF('unfunded
deals'!A:A,">="&date(2006,2,1)). That would count the number in Jan '06 (all
those from 1/1/06 on, minus all those from 2/1/06 on, leaving only those in
January)
 
A

ashley0578

bpeltzer said:
You'd need to replace today()-30 with a calculation that returns the
beginning of the month: today()-day(today())+1.
If you want a specific month, I'd use two countif functions, along the
lines
of
=COUNTIF('unfunded deals'!A:A,">="&date(2006,1,1)) - COUNTIF('unfunded
deals'!A:A,">="&date(2006,2,1)). That would count the number in Jan
'06 (all
those from 1/1/06 on, minus all those from 2/1/06 on, leaving only
those in
January)
Click to expand...

Thanks for the help! Let me explain what I've done in the workbook so
far...
This workbook has three sheets; unfunded deals, funded deals and a
sheet for a recap with just the numbers. I used the countif() statement
to count the number of deals from the today's date minus a certain
amount of days. Column A has the dates in it. The dates are formatted
like April 17, 2006. So, from today - 30 there are 18 unfunded deals
left. While the formula above would work, this would mean that I
would have to change the formula in the workbook once a month. Is
there a way that would look at today's date (April 17, 2006), extract
the month April and count those in Column A with the month April?
 
B

Bob Phillips

=COUNTIF('unfunded deals'!A:A,">="&TODAY()-30)-COUNTIF('unfunded
deals'!A:A,">="&TODAY())

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

ashley0578

Bob said:
=COUNTIF('unfunded deals'!A:A,">="&TODAY()-30)-COUNTIF('unfunded
deals'!A:A,">="&TODAY())
Click to expand...

I see where you are going with this but it's giving me the same result,
and not the actual month-to-date. Any other suggestions??? I know if I
wanted to find the actual month only using a full date in Access, I'd
use Between DateSerial(Year(Date()),Month(Date()),1) And Date() to
yield all the results of this month.
 
B

Bob Phillips

That is effectively what that formula is doing.

Can you give an example of the data, what that formula returns, and what you
expect to see?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

ashley0578

I've attached the file I've been working on (names removed to protect
the innocent!).

What I expect to see on the recap sheet is the total amount of deals
unfunded for the month to date, which for this month would be April 1 -
April 19. Below that, I would see a sum of the total amount of money we
would expect to see from those customers by the end of their loan.

This would be the same for week to today (Sunday thru today).

All of this would be repeated for the funded deals and turndowns.


+-------------------------------------------------------------------+
|Filename: Deal Log example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4658 |
+-------------------------------------------------------------------+
 
Top