Count Formulas and Conditional Formatting

C

cseguin1

New Member
Joined
Jun 19, 2014
Messages
6
I have a spreadsheet where I want to express a progress bar of how many fields have "Yes" marked in them. The yes is entered in fields with Data Validation on them.
I've used the formula =COUNTA(C4:C78)/((COUNT(C4:C78))+(COUNTBLANK(C4:C78))) however the output is coming up with an incorrect percentage. As the example shows below there are 36 out of 75 cells marked with Yes which is 48%, however the formula is returning 92%. Is there any other formula that will provide me and accurate result?


# of DaysDate RequiredComplete (Y/N)
92%
131Monday, 02 March 2015
131Monday, 02 March 2015
262Tuesday, 01 September 2015
5Friday, 05 September 2014
5Friday, 05 September 2014
5Friday, 05 September 2014
5Friday, 05 September 2014
66Monday, 01 December 2014
131Monday, 02 March 2015
131Monday, 02 March 2015
301Tuesday, 25 August 2015Yes
81Tuesday, 21 October 2014Yes
81Tuesday, 21 October 2014Yes
81Tuesday, 21 October 2014Yes
81Tuesday, 21 October 2014Yes
81Tuesday, 21 October 2014Yes
81Tuesday, 21 October 2014Yes
81Tuesday, 21 October 2014Yes
81Tuesday, 21 October 2014Yes
81Tuesday, 21 October 2014Yes
0Yes
0Yes
0Yes
21Monday, 29 September 2014
0Yes
21Monday, 29 September 2014
0Yes
21Friday, 29 August 2014
21Friday, 29 August 2014
21Friday, 29 August 2014
21Friday, 29 August 2014
49Friday, 29 August 2014Yes
49Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014Yes
44Friday, 29 August 2014Yes
15Friday, 29 August 2014
15Friday, 29 August 2014
15Friday, 29 August 2014Yes
15Friday, 29 August 2014Yes
15Friday, 29 August 2014Yes
15Friday, 29 August 2014Yes
15Friday, 29 August 2014Yes
15Friday, 29 August 2014Yes
9Friday, 29 August 2014Yes
5Friday, 29 August 2014
3Friday, 29 August 2014
110Monday, 01 December 2014
110Monday, 01 December 2014
44Friday, 29 August 2014Yes
44Friday, 29 August 2014Yes
44Friday, 29 August 2014Yes
44Friday, 29 August 2014Yes
44Friday, 29 August 2014Yes
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014Yes
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014Yes
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014
44Friday, 29 August 2014Yes
44Friday, 29 August 2014Yes
44Friday, 29 August 2014Yes
44Friday, 29 August 2014Yes

<tbody>
</tbody>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Your formula has a small error (typo)

Try

=COUNTA(C4:C78)/((COUNTA(C4:C78))+(COUNTBLANK(C4:C78)))

or an alternative formula

=COUNTIF(C4:C78,"Yes")/ROWS(C4:C78)

Hope this helps

M.
 
Upvote 0
Opps. I did not see that Marcelo responded. I see his second formula is one step cleaner than mine. excellent.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Looking For a formula to make expense and amount appear every week without having to type week by week
Replies
1
Views
313
wdhayes21
W
R
  • Question
VBA Code to fetch data every particular day in a month
Replies
2
Views
143
ripdaman
R
katsdelite
  • Solved
Help with formula
Replies
4
Views
247
dreid1011
dreid1011
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
334
harzer
H
I
  • Solved
Advice to edit code so deleting by mistke doesnt happen
Replies
7
Views
562
ipbr21054
I

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top