How to calculate % complete across a row with two section of data

Ellyn S

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!

Ellyn S

WAIT!!!! I removed the quotes from around TRUE and it worked!!!

What a relief! Thank you for the suggestions :-)

Lucas Rayala

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

Lucas Rayala

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 auto-populate 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,"")

Ellyn S

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?

Ellyn S

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%

Ellyn S

WAIT!!!! I removed the quotes from around TRUE and it worked!!!

What a relief! Thank you for the suggestions :-)

Lucas Rayala

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