PivotTable count blanks

[martipe1]

I have a table with products and dates:

Example:

Product Date 1 Date 2 Date 3 Date 4 Date 5
A January 02/02/14 BLANK 03/02/13 04/02/12
B January BLANK 02/02/12 03/02/12 BLANK
C January 02/02/13 BLANK 03/02/14 04/02/13
D January 02/02/12 03/02/14 BLANK 04/02/14

I want to count blank dates based on Date 1, this is the output I expect

Date 1 Date 2 Date 3 Date 4 Date 5
January 1 2 1 1


Any idea?

Thanks

 

[ExcelMercy]

How about a:

=countif(RANGE, "Blank")

 

[martipe1]

Thank you for your help

The formula could work, but I would have to manually set the range for every date 1 change, which in my case is not practical

 

[ExcelMercy]

You could set one range, drag to apply it to all the fields you want. Like:

Data Set:

Excel 2010

	A	B	C	D	E	F
1	Product	Month	Date 1	Date 2	Date 3	Date 4
2	A	January	2/2/2014	BLANK	3/2/2013	4/2/2012
3	B	January	BLANK	2/2/2012	3/2/2012	BLANK
4	C	January	2/2/2013	BLANK	3/2/2014	4/2/2013
5	D	January	2/2/2012	3/2/2014	BLANK	4/2/2014
6	E	January	2/2/2014	BLANK	3/2/2013	4/2/2012
7	F	January	BLANK	2/2/2012	3/2/2012	BLANK
8	G	January	2/2/2013	BLANK	3/2/2014	4/2/2013
9	H	January	2/2/2012	3/2/2014	BLANK	4/2/2014
10	I	January	BLANK	BLANK	BLANK	4/2/2012
11	A	February	2/2/2012	2/2/2012	4/2/2013	BLANK
12	B	February	BLANK	3/2/2013	4/2/2014	4/2/2013
13	C	February	3/2/2014	3/2/2012	2/2/2012	4/2/2014
14	D	February	3/2/2013	3/2/2014	BLANK	3/2/2013
15	E	February	3/2/2012	7/9/2014	2/2/2012	3/2/2012
16	F	February	3/2/2014	3/2/2013	BLANK	3/2/2014
17	G	February	BLANK	3/2/2012	3/2/2014	BLANK
18	H	February	4/2/2012	3/2/2014	3/2/2014	BLANK
19	I	February	BLANK	BLANK	BLANK	4/2/2013

<tbody>
</tbody>

Sheet1​

Outcome:

Cell: J2
=COUNTIFS($B$2:$B$19,$I2,C$2:C$19,"Blank")

It checks the array to make sure the month matches and that the text, "Blank" appears in the array. Drag to apply to all the ranges you want.

Excel 2010

	H	I	J	K	L	M
1		Month	Date 1	Date 2	Date 3	Date 4
2	# of Blanks	January	3	5	3	2
3		February	3	1	3	3

<tbody>
</tbody>

Sheet1
​

 

[martipe1]

In your first response, I was thinking about COUNTBLANKS, sorry about that

Thank you very much!!