If Month = today and date = monday, sumproduct

[charliew]

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

 

[Dave Patton]

Excel 2010
	A	B	C	D
1	Criteria
2	Month	4	Monday	2
3				120
4
5	Date		Amount
6	Mon 05-Mar-18		50
7	Mon 09-Apr-18		55
8	Tue 10-Apr-18		60
9	Mon 16-Apr-18		65
10	Tue 17-Apr-18		70
11	Wed 18-Apr-18		75
12
6c

Cell Formulas
Range	Formula
D3	=SUMPRODUCT(--(MONTH(A6:A11)=B2),--(WEEKDAY(A6:A11)=D2),(C6:C11))

 

[charliew]

Excel 2010

	A	B	C	D
Criteria
Month	Monday
Date	Amount

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

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!

 

[Dave Patton]

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

 

[Dave Patton]

Excel 2010
	A	B	C	D
1	Criteria	Mon	Apr	2018
2	Month
3		2	2	2
4
6c

Cell Formulas
Range	Formula
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))

 

[charliew]

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

 

[wideboydixon]

Book1
	A	B	C	D
1	01/01/2018		Monday	5
2	02/01/2018		Tuesday	5
3	03/01/2018		Wednesday	5
4	04/01/2018		Thursday	4
5	05/01/2018		Friday	4
6	06/01/2018		Saturday	4
7	07/01/2018		Sunday	4
8	08/01/2018
9	09/01/2018
10	10/01/2018
11	11/01/2018
12	12/01/2018
13	13/01/2018
14	14/01/2018
15	15/01/2018
16	16/01/2018
17	17/01/2018
18	18/01/2018
19	19/01/2018
20	20/01/2018
21	21/01/2018
22	22/01/2018
23	23/01/2018
24	24/01/2018
25	25/01/2018
26	26/01/2018
27	27/01/2018
28	28/01/2018
29	29/01/2018
30	30/01/2018
31	31/01/2018
Sheet1

Cell Formulas
Range	Formula
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

 

[charliew]

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!

 

[wideboydixon]

You'd have to add three SUMPRODUCTS together something like this:

Book1
	A	B	C	D
1	01/01/2018		Month	February
2	06/01/2018		Monday	1
3	11/01/2018		Tuesday	1
4	16/01/2018		Wednesday	0
5	21/01/2018		Thursday	1
6	26/01/2018		Friday	0
7	31/01/2018		Saturday	1
8	05/02/2018		Sunday	1
9	10/02/2018
10	15/02/2018
11	20/02/2018
12	25/02/2018
13	02/03/2018
14	07/03/2018
15	12/03/2018
16	17/03/2018
17	22/03/2018
18	27/03/2018
19	01/04/2018
20	06/04/2018
21	11/04/2018
22	16/04/2018
23	21/04/2018
24	26/04/2018
25	01/05/2018
26	06/05/2018
27	11/05/2018
28	16/05/2018
29	21/05/2018
30	26/05/2018
Sheet1

Cell Formulas
Range	Formula
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

 

[charliew]

SMASHED IT! thank you!!!