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?


--
ashley0578
------------------------------------------------------------------------
ashley0578's Profile: http://www.excelforum.com/member.php...o&userid=32757
View this thread: http://www.excelforum.com/showthread...hreadid=533059

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)

"ashley0578" wrote
in message ...

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?


--
ashley0578
------------------------------------------------------------------------
ashley0578's Profile:
http://www.excelforum.com/member.php...o&userid=32757
View this thread: http://www.excelforum.com/showthread...hreadid=533059

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)

"ashley0578" wrote:


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?


--
ashley0578
------------------------------------------------------------------------
ashley0578's Profile: http://www.excelforum.com/member.php...o&userid=32757
View this thread: http://www.excelforum.com/showthread...hreadid=533059

bpeltzer Wrote:
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)

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?


--
ashley0578
------------------------------------------------------------------------
ashley0578's Profile: http://www.excelforum.com/member.php...o&userid=32757
View this thread: http://www.excelforum.com/showthread...hreadid=533059

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ashley0578" wrote
in message ...

bpeltzer Wrote:
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)

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?


--
ashley0578
------------------------------------------------------------------------
ashley0578's Profile:
http://www.excelforum.com/member.php...o&userid=32757
View this thread: http://www.excelforum.com/showthread...hreadid=533059

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


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.


--
ashley0578
------------------------------------------------------------------------
ashley0578's Profile: http://www.excelforum.com/member.php...o&userid=32757
View this thread: http://www.excelforum.com/showthread...hreadid=533059

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)

"ashley0578" wrote
in message ...

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


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.


--
ashley0578
------------------------------------------------------------------------
ashley0578's Profile:
http://www.excelforum.com/member.php...o&userid=32757
View this thread: http://www.excelforum.com/showthread...hreadid=533059