+ Reply to Thread
Results 1 to 8 of 8

Counting whether cells are used to fill data from a fixed cell

  1. #1
    Registered User
    Join Date
    06-10-2016
    Location
    Somerset, England
    MS-Off Ver
    20116
    Posts
    4

    Counting whether cells are used to fill data from a fixed cell

    I would like to count whether any 1, 2, 3, 4, or 5 specific cells in a row are used (text or number) and based on that number insert another value from a fixed cell into the 6th cell. Something like this. Is this possible please?



    H I J K
    25 50 75 100


    Jim Peter George Fred 4 names so $K$1
    Jim George Fred 3names so $J$1

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Counting whether cells are used to fill data from a fixed cell

    Welcome to the forum.
    Can you attach a sample workbook with representative data AND a proposed layout solution with a few examples entered in so we can see the logic you want to use?
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    06-10-2016
    Location
    Somerset, England
    MS-Off Ver
    20116
    Posts
    4

    Re: Counting whether cells are used to fill data from a fixed cell

    I'm sorry I have created a sample workbook but can't find a way to insert or attach it.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,542

    Re: Counting whether cells are used to fill data from a fixed cell

    Try

    =INDEX($H$1:$K$1,COUNTA(A1:E1))

    Data in A to E, your numbers in H1:K1

  5. #5
    Registered User
    Join Date
    06-10-2016
    Location
    Somerset, England
    MS-Off Ver
    20116
    Posts
    4

    Re: Counting whether cells are used to fill data from a fixed cell

    This works but my data in the real world is in A1 C1 E1 G1

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

    Re: Counting whether cells are used to fill data from a fixed cell

    Try this one...

    =INDEX(H1:K1,COUNTA(A1,C1,E1,G1))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    06-10-2016
    Location
    Somerset, England
    MS-Off Ver
    20116
    Posts
    4

    Re: Counting whether cells are used to fill data from a fixed cell

    Ok you have sorted it! Thank you very much.

    I have had to modify your version slightly to this to suit my workbook

    =INDEX($AK$3:$AK$10,COUNTA(C103,H103,L103,P103,S103,V103))
    Last edited by Grandsire54; 06-10-2016 at 07:00 PM. Reason: Missed a $

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

    Re: Counting whether cells are used to fill data from a fixed cell

    Good deal. 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. Fill wih fixed cell
    By bpinonto in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2013, 09:52 PM
  2. Counting cells with colour fill, minus the cells with text
    By Crappy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-30-2013, 03:20 AM
  3. How to color-fill blank cells, then once data is entered cell is no-fill
    By hatemail13 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-28-2013, 07:57 PM
  4. counting cells formatted with fill color
    By marcuss3 in forum Excel General
    Replies: 8
    Last Post: 06-06-2012, 09:49 AM
  5. Excel 2007 : Counting Cells based on Fill color
    By mashley in forum Excel General
    Replies: 1
    Last Post: 03-09-2009, 02:21 PM
  6. Fill the Cell with Zeros upto a fixed number of characters
    By hsmeet in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2008, 10:24 PM
  7. Replies: 5
    Last Post: 06-12-2008, 12:39 PM
  8. Counting fixed strings in various cells
    By kylera in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2006, 02:22 AM

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