+ Reply to Thread
Results 1 to 5 of 5

Count cells by colour and category

  1. #1
    Registered User
    Join Date
    02-16-2019
    Location
    London
    MS-Off Ver
    Office 2016
    Posts
    2

    Count cells by colour and category

    So, I have seen many examples around and about where people have counted cells by colour, but I am yet to see one which does colour and category.

    Column B has options of 'Biology', 'Chemistry' and 'Physics'. Column D has 'Your Answer', and Column E has 'Correct Answer'. There are 110 numbers (relating to test questions) down column A.

    What I am trying to achieve is a cell which counts how many Biology answers are correct. Seems like it should be super easy, just maybe I'm having a slow day.

    I imagine the excel-pseudo-code would looks something like:

    =COUNTIFS(B3:B112, "Biology", D3:D112, $D3 = $E3)

    or..

    =COUNT(B3:B112, AND($B3 = "Biology", $D3 = $E3))

    As I'm sure is moderately obvious, I'm not too sure how to just 'check a column', hence all the "$D3" stuff. The cells in Column D are conditionally formatted to be green or red, depending on "Your Answer" vs. "Correct Answer", hence I was also wondering if it is maybe easier to work it out by colour and category.

    The above count info would go into a cell in another table, so the percentage per subject could be worked out as data is entered. Ideally.

    Thank you.
    Last edited by Chamsta; 02-16-2019 at 01:39 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Count cells by colour and category

    I don't believe you need VBA at all. Try this in H3:

    =SUMPRODUCT((Table1[STEM Group]=[@[STEM Group]])*(Table1[Your Answer]=Table1[Correct Answer]))

    and this in M3:

    =SUMPRODUCT((Table1[Skill]=[@[Skill Group]])*(Table1[Your Answer]=Table1[Correct Answer]))

    Welcome to the forum!
    Last edited by AliGW; 02-16-2019 at 01:33 PM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    02-16-2019
    Location
    London
    MS-Off Ver
    Office 2016
    Posts
    2

    Re: Count cells by colour and category

    You're fantastic!! Worked first time. Thank you very much for your help.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Count cells by colour and category

    You are welcome!

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

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Count cells by colour and category

    For the benefit of others, I am attaching the workbook. Please don't remove important bits of the thread - you are rendering it less use to those who might find it useful later. Thanks for your co-operation.
    Attached Files Attached Files

+ 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] Count cells with a fill colour
    By Catalpa in forum Excel General
    Replies: 2
    Last Post: 01-12-2017, 05:22 AM
  2. Count colour of cells and text
    By sneapy94 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2016, 12:43 AM
  3. [SOLVED] Summing by colour and category
    By dna247 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-30-2015, 06:59 AM
  4. Need count for different colour highlighted cells
    By Sankara Narayanan in forum Excel General
    Replies: 5
    Last Post: 11-08-2013, 06:06 AM
  5. Excel 2007 : Count conditioned colour cells
    By Martin Chamberlin in forum Excel General
    Replies: 12
    Last Post: 04-24-2011, 04:22 AM
  6. Count coloured cells - But treat cells with colour and value as 0.5
    By Wabby in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-19-2009, 12:26 PM
  7. Count cells that contain fill colour
    By bondi26 in forum Excel General
    Replies: 4
    Last Post: 11-08-2007, 03:55 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