# Product of Column Counts in a Single Formula

1. ## Product of Column Counts in a Single Formula

Hello everyone,

I have data that looks like this:
A 1 Y
B 2 Z
C 3
_-4

I would like to make a formula that returns the product of the column counts (3*4*2 = 24), but I want to do it in a single cell, without having to put counts in each column.

Here is my unsuccessful attempt:
Formula:
`Please Login or Register  to view this content.`

2. ## Re: Product of Column Counts in a Single Formula

Can you upload a small data sample sheet (Go Advanced>Manage Attachments). Give a couple examples with expected results. I'm not following

3. ## Re: Product of Column Counts in a Single Formula

This is probably way too simplistic, but perhaps...
=COUNT(A:A)*COUNT(B:B)*COUNT(C:C)

4. ## Re: Product of Column Counts in a Single Formula

ChemistB, here is the sample.

Thank you FDibbins. You're right that I am looking that could work for n columns instead of manually entering it.

It's possible that what I'm looking for can't be done, but I've seen array formulas do a lot that I didn't know excel could do, so I'm wondering if there is a way to do this. I tried feeding PRODUCT a list of columns, but when I evaluated the formula, it produced a list of #value errors instead.

5. ## Re: Product of Column Counts in a Single Formula

This array formula**:

=PRODUCT(SUBTOTAL(3,OFFSET(A1,,COLUMN(A1:C6)-COLUMN(A1),ROWS(A1:C6))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

6. ## Re: Product of Column Counts in a Single Formula

Thank you Tony Valko, that works like a charm!

7. ## Re: Product of Column Counts in a Single Formula

You're welcome. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1