+ Reply to Thread
Results 1 to 7 of 7

Returning a value to end column

  1. #1
    Registered User
    Join Date
    03-06-2014
    Location
    Redditch
    MS-Off Ver
    Excel 2003
    Posts
    67

    Returning a value to end column

    I am using counta(c5:c33) to return value if something is type in a cell I do not what to count this if there is an x or Admin type in a cell who if c6 has anx in its not counted and if another cell has admin its not counted I think it bray be be a countif formular but cannot seem to work this out help

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,324

    Re: Returning a value to end column

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: Returning a value to end column

    Your profile says 2003. Is this correct?

    =SUMPRODUCT(--(C5:C33<>"x"),--(C5:C33<>"admin"))

    If you have something higher than 2003...

    =COUNTIFS(C5:C33,"<>x",C5:C33,"<>admin")
    Last edited by jeffreybrown; 12-03-2018 at 04:33 PM.
    HTH
    Regards, Jeff

  4. #4
    Registered User
    Join Date
    03-06-2014
    Location
    Redditch
    MS-Off Ver
    Excel 2003
    Posts
    67

    Re: Returning a value to end column

    This also counts empty cells only want to count cells that have things typed in ececpt x and admin is this possible

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Returning a value to end column

    You can try adding this (in blue):

    =COUNTIFS(C5:C33,"<>x",C5:C33,"<>admin",C5:C33,"<>")

    or if you really do have Excel 2003, then this:

    =SUMPRODUCT(--(C5:C33<>"x"),--(C5:C33<>"admin"),--(C5:C33<>""))

  6. #6
    Registered User
    Join Date
    03-06-2014
    Location
    Redditch
    MS-Off Ver
    Excel 2003
    Posts
    67

    Re: Returning a value to end column

    Many thanks is there a limit to the amount of arrays i can put in =sumproduct as when i get over 5 it returns a HASHTAG VALUE

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Returning a value to end column

    Lol @ hashtag VALUE.

    No limit as far as I am aware. There must be something else going on that is creating the #VALUE! error.

    If you share a small representative sample of your data along with the desired results (which you can enter manually) based on that data, we can help further.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

+ 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. [SOLVED] Retrieving data based on nth column of array and returning first column value
    By truck'ems in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-26-2017, 05:52 PM
  2. Replies: 7
    Last Post: 02-01-2016, 03:49 AM
  3. Replies: 19
    Last Post: 03-15-2014, 04:50 PM
  4. Replies: 5
    Last Post: 09-12-2012, 01:27 PM
  5. [SOLVED] Returning most recent daily data in a column if blanks in column
    By InnesMcc in forum Excel General
    Replies: 6
    Last Post: 11-04-2011, 09:41 AM
  6. [SOLVED] Returning most recent daily data in a column if blanks in column
    By InnesMcc in forum Excel General
    Replies: 2
    Last Post: 11-04-2011, 06:28 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