+ Reply to Thread
Results 1 to 10 of 10

Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

  1. #1
    Registered User
    Join Date
    02-03-2017
    Location
    SE Texas
    MS-Off Ver
    2013
    Posts
    98

    Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

    In the attached spreadsheet (no macros) I have a filterable table where I what to get the number of VISIBLE instances where Column E may have an “X” AND the adjacent Column F is in Red Font. That total should appear in cell F2 (and should reflect the effects of applying any filters).

    I’ve tried to modify several of the scores of formulas and macros I found on the internet but they all seem to want to APPLY conditional formatting to cells or simply count occurrence of a cell with some font color on some condition.

    I am open to Formulas and/or Macros. Any help is appreciated.

  2. #2
    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: Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

    If you are basing your sum on font color in some cells, how was that color applied? If it was applied through Conditional Formatting, you might be able to use the same CF rule in the formula. If the color was applied manually, you will need VBA - formulas cannot see color
    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

  3. #3
    Registered User
    Join Date
    02-03-2017
    Location
    SE Texas
    MS-Off Ver
    2013
    Posts
    98

    Re: Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

    Sorry for the delay, but I have NO idea what's happening to my login sessions!

    Anyway, the colored-cells are hard-coded. None to the macros I found appear to "read" the font color and test that against a text entry in another column to get a count. I might be searching the 'net with some poorly chosen search terms. What would you suggest I search for? I seem to always turn up with something showing me how to "apply" some conditional formatting rather than read it and test against it. Others will count occurrences of text with some font color but I can't seem to combine the hopeful candidates. (gotta reply before i get timed-out AGAIN!)

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

    Hi Joe

    How about attaching your File so we can see what you have. Mock up the expected results.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    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: Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

    Quote Originally Posted by JoeVanGeaux View Post
    Sorry for the delay, but I have NO idea what's happening to my login sessions!
    Make sure you check the "remember me" or "keep me logged in" (whatever it's called lol), just under your login name

  6. #6
    Registered User
    Join Date
    02-03-2017
    Location
    SE Texas
    MS-Off Ver
    2013
    Posts
    98

    Re: Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

    Wow, there's some really weird stuff happening to my sessions, today! I re-attached it.
    Attached Files Attached Files

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

    Hi Joe

    Try this User Defined Function...in Cell F2 add this Formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Place this Code in a Standard Module...
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-03-2017
    Location
    SE Texas
    MS-Off Ver
    2013
    Posts
    98

    Re: Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

    Wow!! It works on example worksheet. I'll inject this into my real world Workbook and get back.
    Thanks!!!

  9. #9
    Registered User
    Join Date
    02-03-2017
    Location
    SE Texas
    MS-Off Ver
    2013
    Posts
    98

    Re: Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

    It took me a little bit to remember from all my previous mistakes with this kind of thing but it works!!!! Thanks many times over!!

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Should I use macro or forumla using SUM, SUBTOTAL, COUNT or COUNTA?

    You're welcome...glad I could help. Thanks for the Rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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] Difference between the SUBTOTAL(103) and COUNTA?
    By hungt in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 06-01-2015, 05:01 PM
  2. [SOLVED] SUBTOTAL in an Array forumla - Counting unique & repeat values disregarding blanks
    By -AJ- in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-26-2014, 08:44 AM
  3. SUBTOTAL and COUNTA
    By hungt in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-02-2013, 08:43 PM
  4. [SOLVED] Count blanks or counta
    By bopsgtir in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-15-2012, 01:43 PM
  5. Replies: 1
    Last Post: 07-13-2010, 08:24 PM
  6. Macro to add count forumla with dynamic range
    By falloutx in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2006, 12:23 AM
  7. [SOLVED] Count or CountA
    By Arturo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2005, 02:06 PM

Tags for this Thread

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