How to know which column has the highest cells left blank

[mwender]

Hi,
I have a table with 20 columns and ~700 rows. A new raw is set for a new project. Each column is depicting the project details.
I'd like to count the number of blanks for each column and get it summarized (pivot table?) so I can draw a pareto chart and find easily the column with the highest number of blanks.
That way I'll be able to come back to the project owner with the highest number of cells left blanks and ask him to edit.

Any suggestion on how to do that?
Thanks

 

[awoohaw]

where are you putting the output for the formula? On the top row above the first row? In a new worksheet?

 

[mwender]

I can't be as a new row as each row is a project...
I was thinking getting the output in a pivot table in some way

 

[awoohaw]

Well, you could just do a pivot table selecting only blank values, but I'm not sure how to do that with an OR mentality.
There is probably a way to make a formula based table I'll try to mock something up.

In the meantime, if possible, can you use the xl2bb add in to paste a mini workbook of your data? Besure to choose mini workbook include all the options,
And when copying please select the range before clicking the mini workbook icon. (you'll understand when you install and see the add in on the ribbon).

 

[awoohaw]

also, what version of excel are you using?

 

[awoohaw]

if you are using 365, then this give you a crosstab report of missing details per project:
It is a clunky formula, and there are probably better ways to get it, but it works. It could probably be easily done with a LET or LAMBDA function:

mr excel questions 17.xlsm
	A	B	C	D	E	F	G	H
1	Project	Detail1	Detail2	Detail3
2	Project1	16	14	23		Project5	Detail1	Detail3
3	Project2	10	24	9		Project10	Detail1	Detail2
4	Project3	21	13	25		Project13	Detail1
5	Project4	20	16	8
6	Project5		14
7	Project6	5	25	8
8	Project7	6	25	21
9	Project8	25	25	23
10	Project9	24	9	22
11	Project10			25
12	Project11	7	7	16
13	Project12	15	21	14
14	Project13		18	13
15	Project14	23	21	8
16	Project15	24	9	12
17
mwender

Cell Formulas
Range	Formula
F2:F4	F2	=TAKE(FILTER(A2:D16,(B2:B16="")+(C2:C16="")+(D2:D16="),""")),,1)
G4,G2:H3	G2	=INDEX($B$1:$D$1,1,FILTER((--(DROP(FILTER($A$2:$D$16,$A$2:$A$16=$F2,""),,1)=0))*(COLUMN(B2:D2)-1), (--(DROP(FILTER($A$2:$D$16,$A$2:$A$16=$F2,""),,1)=0))*(COLUMN(B2:D2)-1)<>0,""))
Dynamic array formulas.