How to calculate % complete across a row with two section of data
I'm trying to calculate % done when calculating across 2 section of a row using the below formula
=COUNTIFS([ACRS Received  MiHIN]1:[APS Feedback Recd  PO]1, true, [Test CCDA Sent]1:[CKS Live]1, true) / 8
The result is 0% when it should be 37.5%
Getting this formula correct will save me a ton of time; there are quite a few rows on this sheet and there are several sheets.
Thank you!
Best Answers

WAIT!!!! I removed the quotes from around TRUE and it worked!!!
What a relief! Thank you for the suggestions :)

Ha! Well, I get that backwards occasionally. Sorry for the confusion.
Answers

Try this:
=(COUNTIFS([ACRS Received  MiHIN]@row:[APS Feedback Recd  PO]@row, "true") + COUNTIFS[Test CCDA Sent]@row:[CKS Live]@row, "true")) / 8
A few things: you need quotes around "true" (that's the actual text behind the checkbox). The countif needs to be broken into two statements and added together, because your data is broken up.
Also, I would suggest using "@row" references instead of the static row references you have. Then, you can create a column formula and this will autopopulate the entire column without you needing to drag or copy the formula every time you have a new row added. To create the column formula, right click the cell with the formula you've created and select the Make Column Formula option. You have to use the @row reference for a column formula  static row references don't work.
You could probably write it like below, because the column you're trying to skip will never be "true", so:
=COUNTIFS([ACRS Received  MiHIN]@row:[CKS Live]@row, "true") / 8
I usually put an iferror wrapper to protect against formula errors, so:
=IFERROR(COUNTIFS([ACRS Received  MiHIN]@row:[CKS Live]@row, "true") / 8,"")

Hi Lucas 
Thank you for the help, unfortunately the function resulted with #unparseable. I copied below what I added to the cell so you could see that I did not mistype it:
=(COUNTIFS([ACRS Received  MiHIN]@row:[APS Feedback Recd  PO]@row, "true") + COUNTIFS[Test CCDA Sent]@row:[CKS Live]@row, "true")) / 8
I also tried the second formula, =COUNTIFS([ACRS Received  MiHIN]@row:[CKS Live]@row, "true") / 8 which resulted in 0.00%
I don't know how to add the additional iferror wrapper to the formula in the cell.
Any other suggestions?

I just tried simply adding =IFERROR(COUNTIFS([ACRS Received  MiHIN]@row:[CKS Live]@row, "true") / 8,"") to the row. I thought it was an additional formula :)
It also resulted in 0.00%

WAIT!!!! I removed the quotes from around TRUE and it worked!!!
What a relief! Thank you for the suggestions :)

Ha! Well, I get that backwards occasionally. Sorry for the confusion.
Help Article Resources
Categories
Check out the Formula Handbook template!