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

[irvine79]

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!

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[bpeltzer]

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]

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)

 

[bpeltzer]

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.

 

[daddylonglegs]

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

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

 

[ashley_miranda]

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 formul

 

[daddylonglegs]

Hi Ashley,

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

can you give an example?

What is basic equal to?

 

[Sandy Mann]

"daddylonglegs" <[email protected]>
wrote in message

...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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


"daddylonglegs" <[email protected]>
wrote in message