countifs - multiple and/or text - same range - 'divide' problem

[PhillyIdol]

Hey all,

I've been looking for a couple days now, as well as tried many different ideas/formula/etc. that I've found on this forum (which I'm new to, so hello to you all ), so apologies ahead of time if this HAS been asked and answered, and I'm just blind as a bat, but here's what we're attempting to do (and failing at so far)...

Basically we have a working formula that calculates the current percentage status (i.e. from 0% to 100% done and with a percentage bar) of a range of tasks that we "Complete" in a spreadsheet, which is this:

• =COUNTIFS(E2:AF2,"Completed")/0.28

...the issue is, we're trying to also include a 'Not Applicable" (or "N/A") text/parameter in this formula. So for example: A Project is currently at 43%. Once the next task is marked "Completed", the percentage bar goes to 46%, BUT - if the next task isn't required, we can mark it as "N/A" and it STILL moves the percentage bar to 46%.

We've tried many varying formulas, like these:

• =COUNTIFS(E2:AF2,{"Completed","N/A"})/0.28

• =COUNTIFS(E2:AF2,"Completed",E2:AF2,"N/A")/0.28

• Etc.

..., but so far we can't seem to crack this one (we've even tried different 'sum' and 'sumifs' ones), so we're hoping someone out there (hopefully without telling us how dumb we are, as we already feel that way ), can show us the right formula.

Thanks in advance to any who are patient enough with a bunch of obvious newbs to assist.

Cheers

[JohnTopley]

Attach a sample workbook (not image)

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[PhillyIdol]

Hey John,

Thanks tons for looking at this. I attached the spreadsheet (I hope) as requested.

Just FYI - the attachment has two sheets/tabs, which I've appended 'before' and 'after' to.

As you'll see in the 'after' sheet, we added the new 'N/A' text parameter changed the formula in the 'Completion' fields to include the entry, but as you'll can see with one of the "COMPLETED/CLOSED" workflows we tested this with, when we use the 'N/A' choice, it drops the bar/counter from 100% to 86%, and we want it to remain/show 100% like the other "COMPLETED/CLOSED" one

Let me know if you require more info, and thanks much for the reply

Cheers

[JohnTopley]

Try

=SUM(COUNTIFS(E3:AF3,{"Completed","N/A"}))/0.28

will give 100%

[PhillyIdol]

Well son-of-a...

I know we tried some types of 'SUM' (and even a few 'SUMIFS') fomulas, and none of them worked for us, but lo and behold, here's the answer.

Thanks tons for the help, and enlightenment, with this.

You're a true guru.

Cheers

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.