Percentage Formula

Beth Faircloth

Good Afternoon,

I am trying to determine the percentage of jobs that have been completed by month, but i get an error for each formula I try. Please see attached. Can anyone help?

Paul Newcome

I don't have access to your sheets. Only the screenshots provided. But based on what you are saying, try using a SUMIFS instead of a COUNTIFS.

=SUMIFS([108-020]:[108-020], Month:Month, "January", Cancelled:Cancelled, 0)

Andrée Starå

Hi Beth,

Can you paste the formula, formulas you're using here?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Beth Faircloth

=Countif([Completed:Completed],=1), IFError(Month:Month, ="January").

I'm probably doing it all wrong. I'm not the best with formulas and still learning. Any help would be appreciated.

Andrée Starå

What do you want the percentage to show more specifically?

Beth Faircloth

I'm wanting to find out the percentage complete by month. I have a column that shows total locations for each month as well.

Andrée Starå

@Beth Faircloth

I missed this! Sorry about that!

Do you still need help?

Beth Faircloth

Hi Andree,

I don't on this one anymore, but I do have another question. I already have columns to total the number of locations that are being completed by month and the total of parts that are being ordered (part# example 108-020). I am wanting to break down how many of each of these parts are being ordered by month. Please see the screenshot of the highlighted formula that is being entered. I keep receiving and error. Could you help with this one?

Andrée Starå

Ok.

I'd be happy to have a look!

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Beth Faircloth

Thank you. I just shared the sheet. "Copy of"

Paul Newcome

Looks like you may be missing a comma after "January".

Beth Faircloth

Thanks. I added the comma and it gives me a total of 191, which is the amount of locations scheduled in January (less cancelled). Doesn't look like my formula is counting the quantity part columns.

Paul Newcome

I assume you are referring to the [108-020] column? If so... Do you have any rows in that column that are scheduled for January and not cancelled that are blank?

Beth Faircloth

Yes I am referring to [108-020]. None of them are blank. Example of what I'm looking for below.

Look at rows 3-6 (Location #'s 65, 136, 153, and 766). Each of these have a quantity of 2 for this part number. However, with the formula it only calculates this as 4 in our total for January rather than 8). Did that help?

Paul Newcome

I don't have access to your sheets. Only the screenshots provided. But based on what you are saying, try using a SUMIFS instead of a COUNTIFS.

=SUMIFS([108-020]:[108-020], Month:Month, "January", Cancelled:Cancelled, 0)

Beth Faircloth

That worked. Thank you so much!

Paul Newcome

Happy to help! 👍️