+ Reply to Thread
Results 1 to 7 of 7

Count cells in column unless they've already been counted

  1. #1
    Registered User
    Join Date
    04-03-2014
    Location
    Eburg, WA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Count cells in column unless they've already been counted

    I need a formula that will count cells in a column that were not already counted for another result. In the attached, I have formulas to count (columns M & N) each decision type (columns D & E) based on type (text). For column T, I have a UDF (cells T7, 9 & 11) to count cells based on color fill of column E, but now I need a formula to count the remaining cells in colum E for results in cells T16 & 18, and U24. The formula will need to count cells (column E) based on text but w/o color fill. Hope this makes sense.

    Can anyone help??

    Thanks,
    JB
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-03-2014
    Location
    Eburg, WA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Count cells in column unless they've already been counted

    In reviewing some other threads, I'm wondering if COUNTIFS would work for my situation. If so, how would the formula look?

    thanks,
    JB

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,310

    Re: Count cells in column unless they've already been counted

    Could you not just use the same UDF, based on color white?
    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
    Registered User
    Join Date
    04-03-2014
    Location
    Eburg, WA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Count cells in column unless they've already been counted

    Unfortunately not becuase the remaining two categories I'm trying to get results for have different types and using white would just include them all rather than give me an accurate count. Unless I'm not understanding your suggestion correctly. Do you have an example formula I can test?

    JB

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,310

    Re: Count cells in column unless they've already been counted

    OK didnt catch that there was more than 1 total required

    What are the criteria for teh 2 totals? maybe provide a sample answer?
    (countif will do just that - count, not sum)

  6. #6
    Registered User
    Join Date
    04-03-2014
    Location
    Eburg, WA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Count cells in column unless they've already been counted

    Hi Ford,

    the criteria are:

    A - count all "S O; No CA/N","S O; Unbrn Vctm","S O; Ref to LE","S O; Anon clr","S O; Previous rprt","S O; same alleg", but do not count any of the blue, red or green (fill) cells

    B - count all "FRS - Non CPS”,“FVS - Non CPS", but do not count any of the blue, red or green (fill) cells


    I've played with a couple formulas and the closest I've gotten to getting the results I wanted is this, but it still didn't pick up all of the cells:

    =COUNTIFS($E$6:$E$54,{"S O; No CA/N","S O; Unbrn Vctm","S O; Ref to LE","S O; Anon clr","S O; Previous rprt","S O; same alleg"},$E$6:$E$54,"<>ColorFunction($W$7)",$E$6:$E$54,"<>ColorFunction($W$9)",$E$6:$E$54,"<>ColorFunction($W$11)",$E$6:$E$54,"<>ColorFunction($W$27)")

    thanks for helping,
    JB

  7. #7
    Registered User
    Join Date
    04-03-2014
    Location
    Eburg, WA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Count cells in column unless they've already been counted

    Sorry, guess I should have clarified:

    Cell T16 = count all "S O; No CA/N","S O; Unbrn Vctm","S O; Ref to LE","S O; Anon clr","S O; Previous rprt","S O; same alleg", but do not count any of the blue, red, green or yellow (fill) cells

    Cell T18 = count all "FRS - Non CPS”,“FVS - Non CPS", but do not count any of the blue, red, green or yellow (fill) cells

    Also, cell W27 has the light yellow fill.

+ 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] If you find the word UPDATED in a column i would like it to be counted
    By HNoel33 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-18-2013, 04:47 PM
  2. [SOLVED] Counted column without loop
    By vizzkid in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-18-2013, 11:41 AM
  3. Date range counted in days in new column
    By boxermack in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-06-2013, 09:33 PM
  4. Replies: 10
    Last Post: 09-27-2010, 11:58 AM
  5. How do I set up the formula so that the A is counted?.-Count If
    By calli in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 02-06-2008, 02:05 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