+ Reply to Thread
Results 1 to 7 of 7

Product of Column Counts in a Single Formula

  1. #1
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    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: copy to clipboard
    Please Login or Register  to view this content.
    1. Include a sample workbook with an example of the result you want
    2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
    3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    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
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    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.
    Attached Files Attached Files

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Product of Column Counts in a Single Formula

    Based on your sample file...

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Product of Column Counts in a Single Formula

    Thank you Tony Valko, that works like a charm!

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Product of Column Counts in a Single Formula

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 01-08-2014, 03:50 PM
  2. [SOLVED] Use a single formula to calculate the product of the top 10 cells with another column
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-30-2013, 04:18 PM
  3. Replies: 3
    Last Post: 09-01-2013, 06:51 PM
  4. Replies: 4
    Last Post: 02-15-2013, 08:02 AM
  5. Replies: 13
    Last Post: 01-20-2010, 05:54 PM

Bookmarks

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