Formula Wanted to Count the number of dates entered in each week

[C_M2840]

Hi All,

I need a formula which will tell me how many dates I have data for during a week number.

Column B is the week number, Column C is the Date, I may have for example 20 different rows of data all from the 18/4/17 which all occurred in week 16, I want to be able to put in a formula that counts all the different dates entered along side Week 16 and tells me how many different ones there are.

Thankyou!

 

[Col Delane]

The COUNTIFS function should return the value you want.
=COUNTIFS( B:B, 16 ) is a single condition formula that will return the number of values in column B = 16. (Replace 16 in the function with a cell reference if desired)
=COUNTIFS( B:B, 16, C:C, 18/4/17 ) includes multiple conditions and will return the number of values in column B = 16 where the corresponding value in column C = 18/4/17. (Replace the argument values in the function with a cell reference if desired)
Check-out Excel's Help (or search the web) for a detailed explanation about this function.

 

[AhoyNC]

Does this mean you want a count of unique dates "counts all the different dates entered"?

If you want unique dates per week # then try the formula in cell E2 of the example below.This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down row as needed.

If you want a count of all dates in the week# then the countif function as suggested by Col Delane.

Excel Workbook
	A	B	C	D	E	F
1		Week #	Date		Unique Date Count	All Dates in Week#
2		16	4/17/2018		4	6
3		16	4/18/2017
4		16	4/18/2017
5		16	4/19/2017
6		16	4/20/2017
7		16	4/20/2017
8		17	4/26/2017		1	2
9		17	4/26/2017
Sheet

 

[C_M2840]

Thankyou the is formula has fixed my problem, however caused another to arise.

For use in pivot tables and charts I need to number of dates data has been entered for within a particular week in every cell in the column, not just at the first entry for the week number, is this possible? E.g. I need every cell in column E where the corresponding row contains data filled.

Thanks in advance.

 

[AhoyNC]

This should do what you want.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C	D	E
1		Week #	Date		Unique Date Count
2		16	4/17/2018		4
3		16	4/18/2017		4
4		16	4/18/2017		4
5		16	4/19/2017		4
6		16	4/20/2017		4
7		16	4/20/2017		4
8		17	4/26/2017		1
9		17	4/26/2017		1
Sheet