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
Product	Month	Date 1	Date 2	Date 3	Date 4
A	January	BLANK
B	January	BLANK	BLANK
C	January	BLANK
D	January	BLANK
E	January	BLANK
F	January	BLANK	BLANK
G	January	BLANK
H	January	BLANK
I	January	BLANK	BLANK	BLANK
A	February	BLANK
B	February	BLANK
C	February
D	February	BLANK
E	February
F	February	BLANK
G	February	BLANK	BLANK
H	February	BLANK
I	February	BLANK	BLANK	BLANK

<tbody>
[TD="align: center"]1[/TD]

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

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

[TD="align: right"]3/2/2013[/TD]
[TD="align: right"]4/2/2012[/TD]

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

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

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

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

[TD="align: right"]3/2/2014[/TD]
[TD="align: right"]4/2/2013[/TD]

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

[TD="align: right"]2/2/2012[/TD]
[TD="align: right"]3/2/2014[/TD]

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

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

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

[TD="align: right"]3/2/2013[/TD]
[TD="align: right"]4/2/2012[/TD]

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

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

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

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

[TD="align: right"]3/2/2014[/TD]
[TD="align: right"]4/2/2013[/TD]

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

[TD="align: right"]2/2/2012[/TD]
[TD="align: right"]3/2/2014[/TD]

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

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

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

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

[TD="align: right"]2/2/2012[/TD]
[TD="align: right"]2/2/2012[/TD]
[TD="align: right"]4/2/2013[/TD]

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

[TD="align: right"]3/2/2013[/TD]
[TD="align: right"]4/2/2014[/TD]
[TD="align: right"]4/2/2013[/TD]

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

[TD="align: right"]3/2/2014[/TD]
[TD="align: right"]3/2/2012[/TD]
[TD="align: right"]2/2/2012[/TD]
[TD="align: right"]4/2/2014[/TD]

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

[TD="align: right"]3/2/2013[/TD]
[TD="align: right"]3/2/2014[/TD]

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

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

[TD="align: right"]3/2/2012[/TD]
[TD="align: right"]7/9/2014[/TD]
[TD="align: right"]2/2/2012[/TD]
[TD="align: right"]3/2/2012[/TD]

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

[TD="align: right"]3/2/2014[/TD]
[TD="align: right"]3/2/2013[/TD]

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

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

[TD="align: right"]3/2/2012[/TD]
[TD="align: right"]3/2/2014[/TD]

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

[TD="align: right"]4/2/2012[/TD]
[TD="align: right"]3/2/2014[/TD]
[TD="align: right"]3/2/2014[/TD]

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

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

</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
Month	Date 1	Date 2	Date 3	Date 4
# of Blanks	January	3	5	3	2
February	3	1	3	3

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

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

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

</tbody>

Sheet1
​

 

[martipe1]

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

Thank you very much!!