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)

[TABLE="width: 306"]
<tbody>[TR]
[TD]status[/TD]
[TD]counta formula[/TD]
[TD]rows formula[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]50.00%[/TD]
[TD]4.17%[/TD]
[/TR]
[TR]
[TD]no
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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?
[Table="width:, class:grid"][tr][td] [/td][td]

C​

[/td][/tr]
[tr][td]

5​

[/td][td]Set 1[/td][/tr]

[tr][td]

6​

[/td][td]yes[/td][/tr]

[tr][td]

7​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]no[/td][/tr]

[tr][td]

9​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

50%​

[/td][/tr]

[tr][td]

11​

[/td][td]set2[/td][/tr]

[tr][td]

12​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td]yes[/td][/tr]

[tr][td]

14​

[/td][td][/td][/tr]

[tr][td]

15​

[/td][td]yes[/td][/tr]

[tr][td]

16​

[/td][td]

100%​

[/td][/tr]

[tr][td]

17​

[/td][td]set 3[/td][/tr]

[tr][td]

18​

[/td][td]yes[/td][/tr]

[tr][td]

19​

[/td][td]no[/td][/tr]

[tr][td]

20​

[/td][td]yes[/td][/tr]

[tr][td]

21​

[/td][td]yes[/td][/tr]

[tr][td]

22​

[/td][td]

75%​

[/td][/tr]

[tr][td]

23​

[/td][td]

0.6​

[/td][/tr]
[/table]
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

[table="width: 500, class: grid, align: center"]
[tr]

[/tr]
[tr]
[td]=SUM(C10,C23,C28)/2[/td]
[/tr]
[tr]
[td][/td]
[/tr]
[tr]
[td]=COUNTIF(C11:C21, "Yes")/COUNTA(C11:C21)[/td]
[/tr]
[tr]
[td]n/a[/td]
[/tr]
[tr]
[td]n/a[/td]
[/tr]
[tr]
[td]n/a[/td]
[/tr]
[tr]
[td]n/a[/td]
[/tr]
[tr]
[td]n/a[/td]
[/tr]
[tr]
[td][/td]
[/tr]
[tr]
[td]n/a[/td]
[/tr]
[tr]
[td]n/a[/td]
[/tr]
[tr]
[td]n/a[/td]
[/tr]
[tr]
[td]n/a[/td]
[/tr]
[tr]
[td]n/a[/td]
[/tr]
[tr]
[td][/td]
[/tr]
[tr]
[td]=COUNTIF(C24:C26, "Yes")/COUNTA(C24:C26)[/td]
[/tr]
[tr]
[td]yes[/td]
[/tr]
[tr]
[td]yes[/td]
[/tr]
[tr]
[td]yes[/td]
[/tr]
[tr]
[td][/td]
[/tr]
[tr]
[td]=COUNTIF(C29:C36, "Yes")/COUNTA(C29:C36)[/td]
[/tr]
[tr]
[td]yes[/td]
[/tr]
[tr]
[td][/td]
[/tr]
[tr]
[td]yes[/td]
[/tr]
[tr]
[td][/td]
[/tr]
[tr]
[td]yes[/td]
[/tr]
[tr]
[td]yes[/td]
[/tr]
[tr]
[td][/td]
[/tr]
[tr]
[td]yes[/td]
[/tr]
[/table]

 

[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?