+ Reply to Thread
Results 1 to 5 of 5

Filtering by fill color of cell and text in column

  1. #1
    Registered User
    Join Date
    05-20-2019
    Location
    Florida, US
    MS-Off Ver
    2018
    Posts
    23

    Filtering by fill color of cell and text in column

    Hello everyone,

    I am trying to filter specific items in the customer column by order numbers that contain a grey fill. For example, I am wanting to see how many order numbers have an order for shoes. I have tried using the CountIf and CountIfs formula. The specific formula I used was =COUNTIFS(F:F, "*shoes*", E:E, "*>1*") The answer I got with that formula was 0. I can count and see there are four order numbers that have an order for shoes, but I want to get the answer with Excel or VBA.

    Is there a way this can be done?

    Screenshot (260).png

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Filtering by fill color of cell and text in column

    You can do this without a formula or VBA using AutoFilter (Data->Filter->Filter by Color). If that won't work for you, you'll need VBA, as Excel formulas cannot read text / fill colors. Unfortunately, VBA is outside my wheelhouse, so hopefully someone more familiar with VBA can provide an answer if needed.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  3. #3
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Filtering by fill color of cell and text in column

    If order numbers are always numeric then there may be no need to rely on cell coloring.

    Here is a solution using two helper columns:

    shoes.png

    Helper-1 in C2 copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This simply propagates "Order Number" to every row

    Helper-2 in D2 copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This concatenates "Order number" and "product"

    The number of distinct customers ordering shoes is in G3 (I generalized this a bit - the search is for whatever is in F2):
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note - this sort of reporting would be vastly easier if you have the latitude to create a different layout of your data. For example:
    Date | Order number | Customer Name | Product

    Let me know whether this helps.

    The attached workbook implements the above.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: Filtering by fill color of cell and text in column

    Maybe,

    =COUNTIFS(F3:F100, "shoes", E2:E99, ">0")

    Regards
    Bosco

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Filtering by fill color of cell and text in column

    @Bosco - your formula works for the OP's example, but if I'm understanding right then I think it under-counts in the event that "First Order" is something other than "Shoes", and Second Order" (or Third Order) order is "Shoes". So in my post#3 example I think it misses the cell B4 shoes.

+ 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. Count column value then adjacent column cell fill color
    By MIGARDEIN in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-09-2015, 01:13 AM
  2. Macro to change cell text color based on darkness of cell fill color
    By cincyshirm61 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-25-2014, 12:11 PM
  3. Replies: 0
    Last Post: 09-26-2012, 01:08 PM
  4. Replies: 3
    Last Post: 09-03-2012, 10:20 PM
  5. Filtering cells, then using fill/color??
    By michelle_car in forum Excel General
    Replies: 4
    Last Post: 06-09-2008, 01:27 PM
  6. [SOLVED] Cell Fill Color and text color - changes for recipient
    By Shadowman13 in forum Excel General
    Replies: 0
    Last Post: 03-08-2006, 07:35 PM
  7. filtering excel worksheet column by color of text
    By SherryRO in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2006, 10:50 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