Summarize Report Function with Dropdown Menu Selections
Hello,
I am looking to determine the instances of a particular dropdown value entered into my sheet by using the report function with the summarize feature in order to determine, based on the weighted value, how large a portion of total any one category may hold. Easily thought about as a pie chart. My dropdown values would be the slices of pie and the weight of each instance of a dropdown value being used would be added to the pie with the corresponding weight determining the size of the slice in relation to the other pieces already added.
Hopefully, that is explained well enough! Thanks and looking forward to getting this solved.
Lee
Best Answer

Hi @Lee Hisey
Is the "Bucket" column a multiselect column?
If so, we can use a HAS function to see if the cell has that selection or not:
=COUNTIF(Bucket:Bucket, HAS(@cell, "2X Spillover"))
Let me know if that works or not.
Cheers,
Genevieve
Answers

Hi @Lee Hisey
Instead of using a Report and the Summary feature, I would suggest using a helper "Metric" sheet. This way you can use a crosssheet COUNTIF formula to count how many times the dropdown value appears in your other sheet, then you can apply your weight to that number in the same calculation.
This way you can use this Metric sheet that houses the weighted number next to the Dropdown text as the source for your Pie Chart.
Ex:
=COUNTIF({Dropdown Column}, "Value 1") * 1.5
See: Crosssheet formulas / COUNTIF Function
Cheers,
Genevieve

@Genevieve P. Thanks for the response! I was able to get the COUNTIF function working but it is not reporting accurately. Is there anything that you are aware of I could do to troubleshoot? Here is what I have entered as the formula =COUNTIF(Bucket:Bucket, "2X Spillover") to 'count' the # of instances of that entry being used in the selected row but it returns a '0' result when there are multiple rows with entries of '2X Spillover.'
It seems like this should solve my underlying issue, if I can get the function reporting correctly.
Thanks!
Lee

Hi @Lee Hisey
Is the "Bucket" column a multiselect column?
If so, we can use a HAS function to see if the cell has that selection or not:
=COUNTIF(Bucket:Bucket, HAS(@cell, "2X Spillover"))
Let me know if that works or not.
Cheers,
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!