Forum Discussion
Multiple Individual Fields on Tab B Calculated for Percentage on Tab A
This may be a Countifs formula but not sure. I have a spreadsheet with a summary page on Tab A with a percentage of "yes" calculated based on answers on Tab B. There are multiple individual cells like C6, C40, C87, C267 - not ranges and I need to count how many Yes's there are in the individual cells on Tab B and report in the summary field on Tab A the percentage of Yes's.
Basically if C6 is Yes, C40 is Yes, C87 is No, and C267 is Yes on Tab B then I need to see on Tab A that that summary line item is 75% complete. The other cells like C7, C8, C9, etc. belong to another summary line that will need the same formula but for that particular line item.
I tried =COUNTIFS('Prioritized Approach Milestones'!C8,"=Yes", ['Prioritized Approach Milestones'!C67,=Yes"]) basically repeating for each cell that applies and I get "The syntax of this name isn't correct." and it highlights the second Prioritized when I click OK.
Is this type of formula possible?
mtrickey See attached.
8 Replies
mtrickey Perhaps the attached example resembles what you are trying to achieve. If not, please clarify.
Note that I have used named ranges to make the formula easier to write and read. An no need to worry about sheet names either.
=COUNTIFS(summary_field,B3,yes_no,"Yes")/COUNTIF(summary_field,B3)Format the cell with this formula as a percentage and you are all set to go.
Riny_van_Eekelen I think what you're saying will work but I'm struggling to get the syntax correct. I filter the field to know which cell to include in the formula so not not how to make the formula look at the applicable rows/cells. I've attached an example like what I'm trying to do.
mtrickey Better to attach the XLSX file rather than a DOCX file with a picture of the Excel file in it.
- Yes, countifs work for that.
=countifs ( criteria_range1 = C:C (this way you are going to select the whole column)
criteria1 "Yes", criteria_range2 = B:B (you can put like a placeholder for every line that you want for the formula to count for example A for rows 6,40,87,267 then the other rows 7,8,9 etc could have another placeholder like B or any other letter. criteria2 a cell with that letter in your summary tab.- Since they’re on different tabs how does that work because it puts the tab name in front of the cell I want to count in the formula? Trying to figure out all the syntax with tab names, ranges (which are individual not continuous cells in one column) and criteria (“=Yes”).
