Attempting to count the total of two drop down menus

AdamD

Hello,

I have two drop down menus, one has company A, company B, Company C. the other drop down menu has option A, option B, option C. I need a cell to tally how many times company A gets option A, B, or C, how many times company B gets option A, B, or C etc.

currently I am using =countifs formula which works with option A, when I add option B to the parameters it always equals 0.

How can I tally all three options for each individual company?

thanks

Mike TV

@AdamD

Is this example what you're after?

Company A formula:

="Option A = " + COUNTIFS(Option:Option, ="Option A", Company:Company, ="Company A") + CHAR(10) + "Option B = " + COUNTIFS(Option:Option, ="Option B", Company:Company, ="Company A") + CHAR(10) + "Option C = " + COUNTIFS(Option:Option, ="Option C", Company:Company, ="Company A")

Company B formula:

="Option A = " + COUNTIFS(Option:Option, ="Option A", Company:Company, ="Company B") + CHAR(10) + "Option B = " + COUNTIFS(Option:Option, ="Option B", Company:Company, ="Company B") + CHAR(10) + "Option C = " + COUNTIFS(Option:Option, ="Option C", Company:Company, ="Company B")

Company C formula:

="Option A = " + COUNTIFS(Option:Option, ="Option A", Company:Company, ="Company C") + CHAR(10) + "Option B = " + COUNTIFS(Option:Option, ="Option B", Company:Company, ="Company C") + CHAR(10) + "Option C = " + COUNTIFS(Option:Option, ="Option C", Company:Company, ="Company C")

You will need to turn word wrap on for the Company A, B, and C columns but it doesn't matter how wide they are it'll put them under each other due to the CHAR(10) which is a carriage return.

Mike TV

@AdamD

Is this example what you're after?

Company A formula:

="Option A = " + COUNTIFS(Option:Option, ="Option A", Company:Company, ="Company A") + CHAR(10) + "Option B = " + COUNTIFS(Option:Option, ="Option B", Company:Company, ="Company A") + CHAR(10) + "Option C = " + COUNTIFS(Option:Option, ="Option C", Company:Company, ="Company A")

Company B formula:

="Option A = " + COUNTIFS(Option:Option, ="Option A", Company:Company, ="Company B") + CHAR(10) + "Option B = " + COUNTIFS(Option:Option, ="Option B", Company:Company, ="Company B") + CHAR(10) + "Option C = " + COUNTIFS(Option:Option, ="Option C", Company:Company, ="Company B")

Company C formula:

="Option A = " + COUNTIFS(Option:Option, ="Option A", Company:Company, ="Company C") + CHAR(10) + "Option B = " + COUNTIFS(Option:Option, ="Option B", Company:Company, ="Company C") + CHAR(10) + "Option C = " + COUNTIFS(Option:Option, ="Option C", Company:Company, ="Company C")

You will need to turn word wrap on for the Company A, B, and C columns but it doesn't matter how wide they are it'll put them under each other due to the CHAR(10) which is a carriage return.

Mike TV

If your dropdown columns are multi-select dropdowns then it'll be a different formula but still do-able I believe. As well, you could make the Company A, B, and C columns into Sheet Summary cells I believe instead.