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!

"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!

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!

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!

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

[email protected]
[email protected] with @tiscali.co.uk


"irvine79" <[email protected]> wrote in message
news:[email protected]...
> 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!

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

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

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

Hi Ashley,

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

can you give an example?

What is basic equal to?

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> ...or you could do the same with SUMPRODUCT....
>
> =SUMPRODUCT(--(A3:A262<=TODAY()),--(A3:A262>TODAY()-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

[email protected]
[email protected] with @tiscali.co.uk


"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> ...or you could do the same with SUMPRODUCT....
>
> =SUMPRODUCT(--(A3:A262<=TODAY()),--(A3:A262>TODAY()-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
>