summing number of date entries in current week

  • Thread starter Thread starter Kevind
  • Start date Start date
K

Kevind

Hi, I've scanned all forums but cannot yet find a solution. Hopin
someone in this forum would be kind enough to help out a novice!

I have a worksheet which is used to enter client details and order
placed. The first clolumn is the date on which the order was placed.
I need to count the number of orders placed within a current week b
filtering the client order dates and return the sum of the order
within the current week.

Can anyone help me with a function for this?

To get the daily and monthly orders was not a problem:

=SUM(IF(LEFT(TEXT(W_Students!A8:A4999,"mmddyy"),2)=TEXT(MONTH(TODAY()),"dd"),1,0)


and

=SUM(IF(LEFT(TEXT(W_Students!A8:A4999,"ddyy"),2)=TEXT(DAY(TODAY()),"dd"),1,0)


respectively ...but the current week scenario is baffling me.

Thanks in advance for any help

Kevi
 
Hi
try the non array formula
=SUMPRODUCT(--(WEEKNUM(W_Students!A8:A4999)=WEEKNUM(TODAY())))
 
Thanks Frank. The erro I get now is #Name!
Sorry, but I'm pretty new to functions in excel.

I have 2 worksheets. The first captures all client details with th
first column being the date of capture.
The second worksheet has totals for all monthly, weekly and dail
sales, based on the date field of the first worksheet, e.g.:
If 3 sales were made on 3 april 2004 and 2 on the 4 april 2004, I wan
a way of calculating the number of sales based on the date captured an
it must be based on a current week. For Access I used to use Betwee
Date()-(WeekDay(Date())-1) AND Date()+(7-WeekDay(Date()))

Any reason why your solution doesn't work? I really appreciate you
posting.

Kind regards
Kevi
 
Hi
sorry should have mentioned that you need to activate the Analysis
Toolpak Add-in for this. Goto 'Tools - Add-in' and check the 'Analysis
Toolpak add-in)
 
Let A2:B30 house the relevant data with dates in A2:A30 and F2 house today's
date...

=SUMPRODUCT(--($A$2:$A$30>=(F21-MOD(F2-2,7))),--($A$2:$A$30<=F2),$B$2:$B$30)

Or: Enter in C2

=WEEKNUM(A2)

and copy down as far as needed. Note that WEEKNUM requires the Analysis
Toolpak add-in (See: Tools|Add-Ins).

Now you can use a SumIf formula instead of the foregoing with SumProduct:

=SUMIF($C$2:$C$30,WEEKNUM(F2),$B$2:$B$30)
 
If you just want to count the dates that meet the current week condition...

=SUMPRODUCT(--($A$2:$A$30>=(F21-MOD(F2-2,7))))

With the column of week numbers added using WEEKNUM...

=COUNTIF($C$2:$C$30,WEEKNUM(F2))

Note: Typo (below)... F21 ==> F2.

Aladin Akyurek said:
Let A2:B30 house the relevant data with dates in A2:A30 and F2 house today's
date...
Click to expand...
=SUMPRODUCT(--($A$2:$A$30>=(F2-MOD(F2-2,7))),--($A$2:$A$30<=F2),$B$2:$B$30)

Or: Enter in C2

=WEEKNUM(A2)

and copy down as far as needed. Note that WEEKNUM requires the Analysis
Toolpak add-in (See: Tools|Add-Ins).

Now you can use a SumIf formula instead of the foregoing with SumProduct:

=SUMIF($C$2:$C$30,WEEKNUM(F2),$B$2:$B$30)


Kevind > said:
Hi, I've scanned all forums but cannot yet find a solution. Hoping
someone in this forum would be kind enough to help out a novice!

I have a worksheet which is used to enter client details and orders
placed. The first clolumn is the date on which the order was placed.
I need to count the number of orders placed within a current week by
filtering the client order dates and return the sum of the orders
within the current week.

Can anyone help me with a function for this?

To get the daily and monthly orders was not a problem:
Click to expand...
=SUM(IF(LEFT(TEXT(W_Students!A8:A4999,"mmddyy"),2)=TEXT(MONTH(TODAY()),"dd")
,1,0))
Click to expand...
=SUM(IF(LEFT(TEXT(W_Students!A8:A4999,"ddyy"),2)=TEXT(DAY(TODAY()),"dd"),1,0
Click to expand...
 
Hi Frank,

Weeknum() doesn't work with Array (implicit, as in Sumproduct(), or explicit)
formulas.

Regards,

Daniel M.
 
Hi Daniel
correct - thanks for this. Should have tested this (§$%&$ Excel).
For the OP: One workaround: Include a helper column which uses Weeknum
for each row and use SUMPRODUCT on this helper column
 
Frank Kabel said:
correct - thanks for this. Should have tested this (§$%&$ Excel).
Click to expand...

Why blame Excel when *you* didn't test?
For the OP: One workaround: Include a helper column which uses
Weeknum for each row and use SUMPRODUCT on this helper column
Click to expand...

Ugh! 'Helper column'?!

Counting how many dates in a range fall in the same week as some particular
date may be done using

=SUMPRODUCT(--(ABS(DateRange-(TODAY()-WEEKDAY(TODAY(),3)+3))<4))

if weeks begin on Monday or

=SUMPRODUCT(--(ABS(DateRange-(TODAY()-WEEKDAY(TODAY(),1)+4))<4))

if weeks begin on Sunday. These require a single pass through the date range
and no extra cells.
 
Harlan said:
Why blame Excel when *you* didn't test?
Click to expand...

:-)
it's easier - but you're right-> my fault

Ugh! 'Helper column'?!

Counting how many dates in a range fall in the same week as some
particular date may be done using

=SUMPRODUCT(--(ABS(DateRange-(TODAY()-WEEKDAY(TODAY(),3)+3))<4))

if weeks begin on Monday or

=SUMPRODUCT(--(ABS(DateRange-(TODAY()-WEEKDAY(TODAY(),1)+4))<4))

if weeks begin on Sunday. These require a single pass through the
date range and no extra cells.
Click to expand...

neat!

Frank
 
Thanks very much for all the replies. I'e used the
=SUMPRODUCT(--(ABS(DateRange-(TODAY()-WEEKDAY(TODAY(),1)+4))<4))
which works perfectly for my needs.

With the above in mind, is there any way to do the same for a curren
day and current month?

At the moment I'm using
=SUM(IF(LEFT(TEXT(W_Students!A5:A500,"mmddyy"),2)=TEXT(MONTH(TODAY()),"dd"),1,0))
in an array - but the syntax looks a bit awkward?? It works, bu
perhaps there's an easier "cleaner" formula for current day and month?

Once again, many thanks to all for your replies.

Regards
kevi
 
Me again...!
I'm struggling with one last formula which is in the same context a
the threads in this post;

With my existing query in mind, I now also need a way to filter th
results by sales person, eg: sum all the entries by current date
current week, current month as above, but also by selecting a name fro
a combo drop down list, I want to be able to select a sales person an
see exactly what he/she has brought it for the current day, week an
month.

The drop downs, etc are no problem, I would just like to know how t
write the function I'm using currently which is
=SUMPRODUCT(--(ABS(DateRange-(TODAY()-WEEKDAY(TODAY(),1)+4))<4))

...to be able to filter a column containing the sales persons names.

The data in the worksheet looks something like
date:title:client:salesperson,etc etc.

Hope this makes a bit of sense, and once again, thanks in advance!

Kevi
 
Hi
try something like
=SUMPRODUCT(--(ABS(DateRange-(TODAY()-WEEKDAY(TODAY(),1)+4))<4),--(name
_range=cell_with_name))
to count the number of entries

or use
=SUMPRODUCT(--(ABS(DateRange-(TODAY()-WEEKDAY(TODAY(),1)+4))<4),--(name
_range=cell_with_name),range_to_sum)
for summing

But you may also consider using a pivot table for this kind of
reporting
 
Back
Top