Total overall non-empty count.
Joelherrmann
-
Raymond PENTIER Posted messages 58207 Registration date Status Contributor Last intervention -
Raymond PENTIER Posted messages 58207 Registration date Status Contributor Last intervention -
Hello,
Is it possible to get a total count of non-empty quantity in a pivot table?
When I select "Count" in the value field settings, it gives me the count of all cells including the empty ones.
I know the COUNTA formula, but I want the information directly linked to my clients' names from my pivot table in order to calculate an average daily sales figure based on three months of sales by dividing the total quantity by the number of days on which there were sales.
Thank you for your help and best regards,
Configuration: Windows / Chrome 78.0.3904.97
Is it possible to get a total count of non-empty quantity in a pivot table?
When I select "Count" in the value field settings, it gives me the count of all cells including the empty ones.
I know the COUNTA formula, but I want the information directly linked to my clients' names from my pivot table in order to calculate an average daily sales figure based on three months of sales by dividing the total quantity by the number of days on which there were sales.
Thank you for your help and best regards,
Configuration: Windows / Chrome 78.0.3904.97
4 answers
Hello,
Normally, if you place the column (the one that contains empty cells) in the "Values" area and format it as Number, it will automatically exclude empty cells from the total.
Make sure you have placed the correct column in the right area when you created your Pivot Table.
Otherwise, please provide your anonymized file so we can see it directly in Excel.
Best regards.
--
Bye bye
“Before dreaming, one must know.”
Normally, if you place the column (the one that contains empty cells) in the "Values" area and format it as Number, it will automatically exclude empty cells from the total.
Make sure you have placed the correct column in the right area when you created your Pivot Table.
Otherwise, please provide your anonymized file so we can see it directly in Excel.
Best regards.
--
Bye bye
“Before dreaming, one must know.”
Hello everyone,
in my opinion, your empty spaces are not really empty.
There must be a space or some other character in there.
Eric
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have for it to work. (the Shadoks)
In addition to saying thank you (yes, yes, it’s done!!!), remember to set it as resolved. Thank you.
in my opinion, your empty spaces are not really empty.
There must be a space or some other character in there.
Eric
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have for it to work. (the Shadoks)
In addition to saying thank you (yes, yes, it’s done!!!), remember to set it as resolved. Thank you.
Thank you for your feedback.
I placed the dates in columns, the company name in rows, and the quantity in values.
Empty cells are created automatically when there were no sales for one of the companies on a date or others made sales.
I placed the dates in columns, the company name in rows, and the quantity in values.
Empty cells are created automatically when there were no sales for one of the companies on a date or others made sales.
In your pivot table, the line is created automatically and therefore there is an empty cell in the case you mentioned. But normally it is not taken into account in your total line.
Then to calculate the average, you put the quantity again in values and in the value field settings, instead of choosing "Count", choose "Average" and it will calculate your average, by company and by date.
I hope I could help you.
Then to calculate the average, you put the quantity again in values and in the value field settings, instead of choosing "Count", choose "Average" and it will calculate your average, by company and by date.
I hope I could help you.
1) Go to https://www.cjoint.com/
2) Click on [Browse] to select your file (max 15 MB)
3) Scroll down to click on the blue button [Create Cjoint link]
4) After a few seconds, the second page will appear with the link in bold; right-click on it and choose "Copy link"
5) Go back to your discussion on CCM, and in your message, paste it.
=>See the guide https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers
There are also:
1) https://mon-partage.fr/
2) https://www.transfernow.net/
--
Retirement is great! Especially in the Antilles...
Raymond (INSA, AFPA)