If Month = today and date = monday, sumproduct

C

charliew

Board Regular
Joined
Feb 20, 2018
Messages
71
Office Version
  1. 365
Platform
  1. Windows
So i have a list of dates and i have been asked to do a running count per month of the amount of dates on each day of the week.

Ive tried sumproduct anf countifs and i cant get any to work

I have a helper column that says what day of the that date is on, to try and keep my formulas simple and keep the size down...and i also have a helper coloumn for whwat month that dae is on for a different formula but cant get it to do what i want!

Please help!

Thank you!!!!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes

Excel 2010
ABCD
1Criteria
2Month4Monday2
3120
4
5DateAmount
6Mon 05-Mar-1850
7Mon 09-Apr-1855
8Tue 10-Apr-1860
9Mon 16-Apr-1865
10Tue 17-Apr-1870
11Wed 18-Apr-1875
12
6c
Cell Formulas
RangeFormula
D3=SUMPRODUCT(--(MONTH(A6:A11)=B2),--(WEEKDAY(A6:A11)=D2),(C6:C11))
 
Upvote 0
Dave Patton said:
Excel 2010
ABCD
Criteria
MonthMonday
DateAmount

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]120[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Mon 05-Mar-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Mon 09-Apr-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]55[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]Tue 10-Apr-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]Mon 16-Apr-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]65[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]Tue 17-Apr-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]Wed 18-Apr-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]75[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
6c​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=SUMPRODUCT(--(MONTH(A6:A11)=B2),--(WEEKDAY(A6:A11)=D2),(C6:C11))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Thank you for this, but what i really want is for the formula to tell me how many dates i have that fall on a specific day of the week so kind of

if a6:a11 = b4 AND a6:a11 = d2 then count so ive got a table for April that looks like :

[TABLE="width: 500"]
<tbody>[TR]
[TD]Monday[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Wednesday
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

(based on your data above)

thanks for your time!
 
Upvote 0
You could just remove the part of the suggested formula that you do not want.
Review the formula with Excel's Formula Evaluate.

=SUMPRODUCT(--(MONTH(A6:A11)=B2),--(WEEKDAY(A6:A11)=D2))
 
Upvote 0

Excel 2010
ABCD
1CriteriaMonApr2018
2Month
3222
4
6c
Cell Formulas
RangeFormula
B3=SUMPRODUCT(--(MONTH(A6:A11)=4),--(WEEKDAY(A6:A11)=2))
C3=SUMPRODUCT(--(TEXT(A6:A11,"dddmmmyyy")="MonApr2018"))
D3=SUMPRODUCT(--(TEXT(A6:A11,"dddmmmyyy")=B1&C1&D1))
 
Upvote 0
Hi there,

I appreciate all your help and time on this but none of the are working. i'm either getting a #Value ! error or a #Name ! error. i think ill just omit this from my spreadsheet for now!!
 
Upvote 0

Book1
ABCD
101/01/2018Monday5
202/01/2018Tuesday5
303/01/2018Wednesday5
404/01/2018Thursday4
505/01/2018Friday4
606/01/2018Saturday4
707/01/2018Sunday4
808/01/2018
909/01/2018
1010/01/2018
1111/01/2018
1212/01/2018
1313/01/2018
1414/01/2018
1515/01/2018
1616/01/2018
1717/01/2018
1818/01/2018
1919/01/2018
2020/01/2018
2121/01/2018
2222/01/2018
2323/01/2018
2424/01/2018
2525/01/2018
2626/01/2018
2727/01/2018
2828/01/2018
2929/01/2018
3030/01/2018
3131/01/2018
Sheet1
Cell Formulas
RangeFormula
C1=DATE(2018,4,16)
C2=C1+1
D1=SUMPRODUCT(--(WEEKDAY($A$1:$A$31)=WEEKDAY($C1)))


Column C custom formatted as "dddd". Is that more of what you're after?

WBD
 
Upvote 0
We've got it working! thanks you for your help...How do i get it to do the same with a similar data range from a different sheet at the same time? My plan in to eventually have a drop down month menu and it tell me how many inquiries were sent on everyday of the week for that month, but i have data being put into 3 separate sheets....

thanks again!
 
Upvote 0
You'd have to add three SUMPRODUCTS together something like this:


Book1
ABCD
101/01/2018MonthFebruary
206/01/2018Monday1
311/01/2018Tuesday1
416/01/2018Wednesday0
521/01/2018Thursday1
626/01/2018Friday0
731/01/2018Saturday1
805/02/2018Sunday1
910/02/2018
1015/02/2018
1120/02/2018
1225/02/2018
1302/03/2018
1407/03/2018
1512/03/2018
1617/03/2018
1722/03/2018
1827/03/2018
1901/04/2018
2006/04/2018
2111/04/2018
2216/04/2018
2321/04/2018
2426/04/2018
2501/05/2018
2606/05/2018
2711/05/2018
2816/05/2018
2921/05/2018
3026/05/2018
Sheet1
Cell Formulas
RangeFormula
D2=SUMPRODUCT((TEXT(Sheet1!$A$1:$A$30,"mmmm")=$D$1)*(TEXT(Sheet1!$A$1:$A$30,"dddd")=$C2))+SUMPRODUCT((TEXT(Sheet2!$A$1:$A$30,"mmmm")=$D$1)*(TEXT(Sheet2!$A$1:$A$30,"dddd")=$C2))+SUMPRODUCT((TEXT(Sheet3!$A$1:$A$30,"mmmm")=$D$1)*(TEXT(Sheet3!$A$1:$A$30,"dddd")=$C2))


WBD
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Divide Current Month into weeks
Replies
3
Views
251
Armstrong_N14
A
E
  • Question Question
Calculating Sick Days for New Employees
2
Replies
12
Views
137
Joe4
Joe4
F
  • Solved
Completions % by Month and Current Year
Replies
8
Views
268
Joe4
Joe4
S
  • Question Question
Sumproduct - to count occasions in a date range
Replies
3
Views
421
Cubist
Cubist
M
  • Solved
Create sheet for every day of month using Master template, name each sheet as per day of month. VBA
Replies
4
Views
475
John_w
J

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top