Summarize Report Function with Dropdown Menu Selections

Lee Hisey

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

Genevieve P.

Hi @Lee Hisey

Is the "Bucket" column a multi-select 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

Genevieve P.

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 cross-sheet 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: Cross-sheet formulas / COUNTIF Function

Cheers,

Genevieve

Lee Hisey

@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

Genevieve P.

Hi @Lee Hisey

Is the "Bucket" column a multi-select 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