COUNTIF & COUNTA w/ IF, OR, AND statement

[dch5876]

I don't even know if this is possible.

I have a cell that is percentage of a task completed. It counts the yes's in a range and returns percentage complete. When all of the cells are marked “Yes” it says 100%.

Original formula for Over All %:
=COUNTIF(C10:C33, “Yes”)/COUNTA(C10:C33)

Now there is a variant, where you can either enter yes forC10:C20 or it yes for C21:C24. But never will you enter yes in both ranges. To Throw an additional curve ball C25:C33 always need to be figured in. I split up the problem into something like this and have hidden rows 10,23 & 28 for the task set calculations.

Over All %:
=SUM(C10,C23,C28)/2

Task Set 1%:
=COUNTIF(C11:C21, "Yes")/COUNTA(C11:C21)

Task Set 2%:
=COUNTIF(C24:C26, "Yes")/COUNTA(C24:C26)

Task Set 3%:
=COUNTIF(C29:C36, "Yes")/COUNTA(C29:C36)

It does work, but is there any way to consolidate this into a single formula?

 

[liveinhope]

before I start looking at your variant issue need to clarify the original formula ie =COUNTIF(C10:C33, “Yes”)/COUNTA(C10:C33)
the COUNTA(C10:C33) part only includes cells with a value , empty cells are not included . so in this ex maple below (2 cells only c10 and c11 populated , c12 to c33 blank) you get 50% Is that what you want ?
If you want to have the percentage based on all the cell in c10 to c33 including the blank ones this try =COUNTIF($C$10:$C$33,"YES")/ROWS($C$10:$C$33)

status	counta formula	rows formula
YES	50.00%	4.17%
no

<tbody>
</tbody>

 

[FDibbins]

To get the individual %, you will need to do them on their own

To do the overall, why not just extend the range and subtract the subtotals? Something like this?

	C​
5​	Set 1
6​	yes
7​
8​	no
9​
10​	50%​
11​	set2
12​
13​	yes
14​
15​	yes
16​	100%​
17​	set 3
18​	yes
19​	no
20​	yes
21​	yes
22​	75%​
23​	0.6​

C10 etc =COUNTIF(C6:C9,"yes")/COUNTA(C6:C9)
C23=COUNTIF($C$6:$C$22,"yes")/(COUNTA(C6:C22)-COUNT(C6:C22))

 

[dch5876]

I'm using a data validation list with a yes, n/a & no drop down. All of the cells read no until you select yes or n/a. I wasn't planning on having any blank cells. with your suggested route for counting all cells even if blank is there any way to do it with specific cells instead of a range of cells? My ultimate goal is to only have the formula in one cell instead of breaking it out into 4 different locations. Not sure if that helps you better understand my goal or not. Thank you for the help in advance and offering up additional ideas on accomplishing my goal.

 

[dch5876]

Thanks for the suggestion. If my dream of a single cell doesn't work this is definitely a good alternative to how I have it setup now.

 

[dch5876]

Here is an example of what I have going on right now

=SUM(C10,C23,C28)/2

=COUNTIF(C11:C21, "Yes")/COUNTA(C11:C21)

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

=COUNTIF(C24:C26, "Yes")/COUNTA(C24:C26)

yes

yes

yes

=COUNTIF(C29:C36, "Yes")/COUNTA(C29:C36)

yes

yes

yes

yes

yes

 

[FDibbins]

Thanks for the suggestion. If my dream of a single cell doesn't work this is definitely a good alternative to how I have it setup now.

1. It is hard to tell who you are referring to, unless you include a name
2. what exactly do you mean by "a single cell"? what will that contain, and can you show an example?