SUMIF within date range as a function of today()'s date
 

Column A = Each day's date for this year
Column Q = Each day's production goal.

I am trying to come up with a cell that will look at today's date and sum
the production goal figures between today and the 1st of the month.

I'm thinking I need to summarize the production goals prior to the 1st, then
subtract that figure from the YTD figure.

Thanks!

SUMIF within date range as a function of today()'s date
 

For the total to date this month try:

=SUM((MONTH(A1:A1000)=MONTH(TODAY()))*Q1:Q1000)

This is an array formula so it must be entered with Crtl + Shift + Enter not
just a simple Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"irvine79" wrote in message
...
Column A = Each day's date for this year
Column Q = Each day's production goal.

I am trying to come up with a cell that will look at today's date and sum
the production goal figures between today and the 1st of the month.

I'm thinking I need to summarize the production goals prior to the 1st,
then
subtract that figure from the YTD figure.

Thanks!

SUMIF within date range as a function of today()'s date
 

"irvine79" wrote:

Column A = Each day's date for this year
Column Q = Each day's production goal.

I am trying to come up with a cell that will look at today's date and sum
the production goal figures between today and the 1st of the month.

I'm thinking I need to summarize the production goals prior to the 1st, then
subtract that figure from the YTD figure.

Thanks!

SUMIF within date range as a function of today()'s date
 

Figured it out, I just had to subtract the cummulative annual figure prior to
the first of the month from the total annual figure to date.

the formula:

=SUMIF(A3:A262,"<"&TODAY(),Q3:Q262)-SUMIF(A3:A262,"<"&DATE(YEAR(TODAY()),MONTH(TODAY() ),1),Q3:Q262)

"irvine79" wrote:

Column A = Each day's date for this year
Column Q = Each day's production goal.

I am trying to come up with a cell that will look at today's date and sum
the production goal figures between today and the 1st of the month.

I'm thinking I need to summarize the production goals prior to the 1st, then
subtract that figure from the YTD figure.

Thanks!

SUMIF within date range as a function of today()'s date
 

You've basically got it right... something like
=sumif(a:a,"<="&today(),q:q)-sumif(a:a,"<"&(today()-day(today()+1),q:q). The
logic is add up all the goals for today or earlier, and subtract all the
goals from prior to this month. today()-day(today()) gets you the last day
of the prior month, so adding one gets you the first day of this month. If
you're interested in a particular date other than today, just change today()
in the formula to a cell containing the date of interest.

"irvine79" wrote:

Column A = Each day's date for this year
Column Q = Each day's production goal.

I am trying to come up with a cell that will look at today's date and sum
the production goal figures between today and the 1st of the month.

I'm thinking I need to summarize the production goals prior to the 1st, then
subtract that figure from the YTD figure.

Thanks!

SUMIF within date range as a function of today()'s date
 

....or you could do the same with SUMPRODUCT....

=SUMPRODUCT(--(A3:A262<=TODAY()),--(A3:A262TODAY()-DAY(TODAY())),Q3:Q262)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568750

SUMIF within date range as a function of today()'s date
 

Hi

I have to give a salry increase for 'X'person

Cell A - Amount to be given

I have to distribute this amount in
1. Basic
2. HRA (calculated@25%basic)
3. Provident Fund (calculated at 12% basic)

Please provide a formula


--
ashley_miranda
------------------------------------------------------------------------
ashley_miranda's Profile: http://www.excelforum.com/member.php...o&userid=37155
View this thread: http://www.excelforum.com/showthread...hreadid=568750

SUMIF within date range as a function of today()'s date
 

Hi Ashley,

normally you'd start a new thread for an unrealted query.....

can you give an example?

What is basic equal to?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568750

SUMIF within date range as a function of today()'s date
 

"daddylonglegs"
wrote in message
news:daddylonglegs.2c4k43_1154878207.9263@excelfor um-nospam.com...

...or you could do the same with SUMPRODUCT....

=SUMPRODUCT(--(A3:A262<=TODAY()),--(A3:A262TODAY()-DAY(TODAY())),Q3:Q262)


Yes good thinking. I started trying SUMPRODUCT() but I was fixated on the
MONTH() function.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"daddylonglegs"
wrote in message
news:daddylonglegs.2c4k43_1154878207.9263@excelfor um-nospam.com...

...or you could do the same with SUMPRODUCT....

=SUMPRODUCT(--(A3:A262<=TODAY()),--(A3:A262TODAY()-DAY(TODAY())),Q3:Q262)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568750