Pivot app - and Count function

Meredith Rhodes

I've created a pivot table to summarize the number of task completions in a month. From this, I want to calculate the average monthly completions.

The problem is, the table does not put a '0' in the months with no completions, so the averages are not calculated correctly (see below - 1 isn't the monthly average when only 1 was completed that year).

Maybe the answer is 'just add the zero' - but isn't 0 a real count?

When I formatted my pivot table and just used the sum function for the child rows - you can see it isn't counting blanks as months.

I tried changing my formula on the source sheet that is wrapped in an IFERROR to force blanks - changed it to force "0" - but that isn't it.

As a workaround, I've made automations to change blank cells to 0, but I'm thinking that 0 should just be a number that is automatically input on the pivot table.

I wanted to check in with the SmartSheet community to see if I'm missing something.

Thank you!

Meredith

Andrée Starå

Hi @Meredith Rhodes

I hope you're well and safe!

Can you share the formulas used and some more screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Meredith Rhodes

Hi @Andrée Starå!

I'll see if I can add more information here:

This is a screenshot of the source sheet with the Type of Submission, Date Submitted, Date Completed Columns - and the helper columns I've created for Month and Year Submitted and Month and Year Completed (I'll add the formulas below)

Month and Year Submitted

=IFERROR(IF(MONTH([Date Submitted]@row) = 1, "January", IF(MONTH([Date Submitted]@row) = 2, "February", IF(MONTH([Date Submitted]@row) = 3, "March", IF(MONTH([Date Submitted]@row) = 4, "April", IF(MONTH([Date Submitted]@row) = 5, "May", IF(MONTH([Date Submitted]@row) = 6, "June", IF(MONTH([Date Submitted]@row) = 7, "July", IF(MONTH([Date Submitted]@row) = 8, "August", IF(MONTH([Date Submitted]@row) = 9, "September", IF(MONTH([Date Submitted]@row) = 10, "October", IF(MONTH([Date Submitted]@row) = 11, "November", IF(MONTH([Date Submitted]@row) = 12, "December")))))))))))) + " " + YEAR([Date Submitted]@row), "")

Month and Year Completed

=IFERROR(IF(MONTH([Date Completed]@row) = 1, "January", IF(MONTH([Date Completed]@row) = 2, "February", IF(MONTH([Date Completed]@row) = 3, "March", IF(MONTH([Date Completed]@row) = 4, "April", IF(MONTH([Date Completed]@row) = 5, "May", IF(MONTH([Date Completed]@row) = 6, "June", IF(MONTH([Date Completed]@row) = 7, "July", IF(MONTH([Date Completed]@row) = 8, "August", IF(MONTH([Date Completed]@row) = 9, "September", IF(MONTH([Date Completed]@row) = 10, "October", IF(MONTH([Date Completed]@row) = 11, "November", IF(MONTH([Date Completed]@row) = 12, "December")))))))))))) + " " + YEAR([Date Completed]@row), "")

(NOTE: I've wrapped it in an IFERROR, then leave it blank - I've changed this to be "then make it 0", but this doesn't change the result)

I'm attempting to summarize the average submissions and completions per month. Which means I need a count of the number of submissions and completions per month. In the pivot table for Completions, I've used the following:

Rows: Month and Year Completed

Columns: Type of Submission

Values: Month and Year Completed (COUNT)

This is the Resultant table after I preview and click "Create":

^^^ Of course it requires some formatting to get the dates in order, ADD months that aren't accounted for, and create parent child relationships the Year - but when you do this and insert the =AVG(CHILDREN()), it does not calculate the blanks as zero (it's not an average).

Ideally - I get 0's in the blank cells to to make calculations work (and I've done that via an automation on the submissions table). Why would the Count Function in the pivot table think the absence of completions or submissions in a month isn't a count of zero?

Thanks!

Meredith

Genevieve P.

Hi @Meredith Rhodes

You're correct: if there is no data for the Pivot to calculate on, it will simply return a blank cell for that Type column as the COUNT value instead of 0.

Your note of using workflows to add 0's to any blank cells is exactly what I would suggest for this scenario!

Cheers,

Genevieve