Need help in ignoring excel/pivot counting Blank Cells..

[vasanthkumar1979]

Dear Experts,

I need help in the attached excel sheet. I have a huge data where I need to filter particular status. I had put IF condition with "" value (=IF(E2="Open","1",IF(E2="WIP","1","")).

When I check the count, the total counts the blank cells too. The same issue happens in Pivot.

Any suggestion on how to get rid of blank cell?

Test File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Priority	Issue Description	Ownership	Status	Aging
2	08-Aug-22	High	Issue No.1	A	Open	1
3	08-Aug-22	High	Issue No.2	B	Close
4	08-Aug-22	High	Issue No.3	C	WIP	1
5	08-Aug-22	High	Issue No.4	A	Open	1		Formula used	IF(E2="Open","1",IF(E2="WIP","1",""))
6	08-Aug-22	High	Issue No.5	B	Close
7	08-Aug-22	High	Issue No.6	C	Close			CountA=	9
8	08-Aug-22	High	Issue No.7	A	Open	1
9	08-Aug-22	High	Issue No.8	B	Close			I understand "" value will be counted. Is there any way the blank cell shoul not be counted while doing CountA?
10	08-Aug-22	High	Issue No.9	C	WIP	1
11						9
12
13
14
15
16	Count of Aging	Column Labels
17	Row Labels	A	B	C	Grand Total
18	Issue No.1	1			1
19	Issue No.2		1		1
20	Issue No.3			1	1
21	Issue No.4	1			1
22	Issue No.5		1		1
23	Issue No.6			1	1
24	Issue No.7	1			1
25	Issue No.8		1		1
26	Issue No.9			1	1
27	Grand Total	3	3	3	9
28
29
Consolidated

Cell Formulas
Range	Formula
F2:F10	F2	=IF(E2="Open","1",IF(E2="WIP","1",""))
F11	F11	=COUNTA(F2:F10)

 

[Joe4]

Instead of using a COUNTA function, why not use COUNTIF instead, counting the ones equal to "1"?

=COUNTIF(F2:F10,"1")

 

[vasanthkumar1979]

Hello Joe,

The idea is to summarize in the Pivot Table. I just showed Count to show its counting the blanks too. When I make the Pivot Table, the blanks also will be counted. In order to get rid of that, I have to go to blank cells and delete unseen "" which will be an additional task when the data is huge.

 

[Joe4]

Can you change your initial formula to this?

=IF(OR(E2="Open",E2="WIP"),1,0)

And then instead of trying to do a COUNT in your Pivot table, try doing a SUM instead?

 

[vasanthkumar1979]

That doesn't help too. When I pull it to a pivot table. Row Labels will be "Issue Description", Column labels will be "Ownership" and Values will be "Aging".

Expected result should be 5 (Total), but Pivot shows 9 because it counts 0 as a value. Cell has to be empty to return the total value as 5.

 

[Joe4]

That doesn't help too. When I pull it to a pivot table. Row Labels will be "Issue Description", Column labels will be "Ownership" and Values will be "Aging".

Expected result should be 5 (Total), but Pivot shows 9 because it counts 0 as a value. Cell has to be empty to return the total value as 5.

I said to choose the "SUM" option instead of the "COUNT" option in your Pivot Table.
See: Sum values in a PivotTable

If that is not what you want, show us an example of your original data, and your desired output.
I think things may be confusing because of all the "extra" stuff you added in there for demonstration purposes, but don't really want there.
It is difficult to differentiate the two with what you really have and what you really want, without any of the "extra" stuff.

 

[vasanthkumar1979]

Okay! Here is what I am expecting! Left is my Raw data and Right is what I am expecting in Pivot table.

Test File.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Raw Data							Expected Result in Pivot
2	Date	Priority	Issue Description	Ownership	Status	Aging		Issue Description	Line Items	Status
3	08-Aug-22	High	Issue No.1	A	Open	1		Issue No.1	1	1
4	08-Aug-22	High	Issue No.2	B	Close	0		Issue No.2	1
5	08-Aug-22	High	Issue No.3	C	WIP	1		Issue No.3	1	1
6	08-Aug-22	High	Issue No.4	A	Open	1		Issue No.4	1	1
7	08-Aug-22	High	Issue No.5	B	Close	0		Issue No.5	1
8	08-Aug-22	High	Issue No.6	C	Close	0		Issue No.6	1
9	08-Aug-22	High	Issue No.7	A	Open	1		Issue No.7	1	1
10	08-Aug-22	High	Issue No.8	B	Close	0		Issue No.8	1
11	08-Aug-22	High	Issue No.9	C	WIP	1		Issue No.9	1	1
12								Grand Total	9	5
13
Consolidated

Cell Formulas
Range	Formula
F3:F11	F3	=IF(OR(E3="Open",E3="WIP"),1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F1048576	Cell Value	="Blank"	text	NO

 

[Joe4]

That does not look like a very robust example.
Each issue has exactly one line item, so your Pivot table has the exact same number of rows as your original table.
If this is actually how things actually are, the Pivot table is pretty redundant and totally unnecessary.

Can you show a better example, that is more indicative of the different situations that may actually experience?
Show some of the more complex scenarios that you may have?