How to count single select dropdown products within 3 columns.

Brenda Peterson

I have the same list of 28 products in a single select drop down in 3 columns. (3 columns are necessary b/c they each can be used in different amounts and those mix rates are in neighboring columns.) I want to count the number of times a product was used within three different columns and added together.

DKazatsky2

Hi @Brenda Peterson,

In a separate sheet, setup a column that lists all the products and a column that will have the formula for the totals. In my example I am showing 5 products - you can just continue the pattern for the rest.

Here is the formula: =COUNTIF({Choice 1}, @cell = Primary@row) + COUNTIF({Choice 2}, @cell = Primary@row) + COUNTIF({Choice 3}, @cell = Primary@row)

Make sure to setup the correct reference to each of the 3 columns.

I hope this helps,

Dave

Ward.Hively

Hi Brenda,

Imagining the sheet to look something like this and without further details I am speculating but this is the direction I would take. →

Product 1 | Quantity 1 | Product 2 | Quantity 2 | Product 3 | Quantity 3|

Apple | 10 | Orange | 9 | Banana | 3 |

You have many options, I will give you 3.

The easiest answer is to create a report with a count on the product columns. It "does the job" but fails to completely solve the problem.

The second answer is creating a helper sheet and manually generating the total sum.

1. Create a grid sheet
2. Copy the drop-down choices
3. Paste them into the primary column
4. Use a column formula like =CountIF({Product 1 Column Look Up Sheet}, primary@row)+CountIF({Product 2 Column Look Up Sheet}, primary@row) + CountIF({Product 3 Column Look Up Sheet}, primary@row)
5. Sum the counts in a separate cell.

The third and cleanest answer is to write a formula in the sheet summary for each of the products to count the total of each product.

Good luck!