Help with a Count formula

[Tigolivier]

Hello,

I have a set of data that I need to have a formula in my 1st row that will tell me the % of the cells that are filled in.

I achieve this with a =COUNTA pretty easily but there's some issues in a couple of columns where I expect some cells to be blank.

I'd like this formula to read in Column E and only consider cells that are "True", in other columns I will need the formula to only consider cells that are "False".

This will take me two formulas but I can't seem to work it out.

 

[Joe4]

I think we need more details. It would probably be best if you could post a small example of your data, along with the expected results of your data.
Seeing that should help clarify exactly how you want this to work.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Tigolivier]

Hi Joe4,
Here is the data sample, so the formula should live in Row1, in Column C you will see that I have True / False in there, based on that value I want the formula to give me the % of completion of the data.
So in E1, the formula should give me 100% because I want to validate that all my rows with "True" are filled out.
In F1, the formula should return 50%, because in that column I need all the rows with "False" filled out.
I understand this probably takes two formulas and not one, at this point I am more than ok with that.

Book2
	A	B	C	D	E	F	G	H
1	Formula Here	Formula Here	Formula Here	Formula Here	Formula Here	Formula Here	Formula Here	Formula Here
2	Brand	Product Category	True / False	SKU	Test Data 1	Test Data 2	Test Data 3	Test Data 4
3	Food	Fruit	TRUE	Banana	1		1
4	Food	Fruit	FALSE	Banana-1		Bli		1
5	Food	Fruit	FALSE	Banana-2		Bla		1
6	Food	Fruit	FALSE	Banana-3		Blo		1
7	Food	Fruit	TRUE	Orange	1		2
8	Food	Fruit	FALSE	Orange-1		1		1
9	Food	Fruit	FALSE	Orange-2		2		1
10	Food	Fruit	FALSE	Orange-3		3		1
11	Food	Fruit	FALSE	Orange-4		4		1
12	Food	Fruit	FALSE	Orange-5		5		1
13	Food	Fruit	FALSE	Orange-6		6		1
14	Food	Fruit	TRUE	Carrot	1		3
15	Food	Fruit	FALSE	Carrot-1				3
16	Food	Fruit	FALSE	Carrot-2				3
17	Food	Fruit	FALSE	Carrot-3				3
18	Food	Fruit	TRUE	Tomato	1		4
19	Food	Fruit	FALSE	Tomato-1				5
20	Food	Fruit	FALSE	Tomato-2				5
21	Food	Fruit	FALSE	Tomato-3				5
22	Food	Fruit	FALSE	Tomato-4				5
23	Food	Fruit	FALSE	Tomato-5				5
24	Food	Fruit	FALSE	Tomato-6				5
Sheet1

 

[Joe4]

I am not quite sure I understand stand that in column E, you want the percentage of TRUE values with cells filled out in column E,
but in column F, you want the percentage of FALSE values with cells filled out in column F.
Maybe you want each column to alternate between TRUE/FALSE? Not sure your logic there.

However, these formula return what you want:

In cell E1:

=COUNTIFS($C:$C,TRUE,E:E,"<>")/COUNTIFS($C:$C,TRUE)

In cell F1:

=COUNTIFS($C:$C,FALSE,F:F,"<>")/COUNTIFS($C:$C,FALSE)

If it is truly an alternating check by column (TRUE/FALSE/TRUE/FALSE/, etc).
then you could put this one formula in cell E1 and copy across for all columns in that row:

=COUNTIFS($C:$C,ISODD(COLUMN()),E:E,"<>")/COUNTIFS($C:$C,ISODD(COLUMN()))