+ Reply to Thread
Results 1 to 6 of 6

Countifs to compare two columns and return the total number of colored cells

  1. #1
    Registered User
    Join Date
    09-20-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Countifs to compare two columns and return the total number of colored cells

    Hi,

    I'm hoping someone can help me out with this - I've been trying to figure it out for hours with no luck!

    I have a spreadsheet (actually several) which have unit types (1 bedrooms, 2, bedrooms, etc) column A and colours in column B indicating their status (black=vacant, blue=occupied, etc). I have used this spreadsheet for years with a user defined formula called CountCol to basically count the colours to summarize the status. So a formula like COUNTCOL(B:B,1) will count all the black cells and return a 2 if there are 2 black cells. This basic type of counting works great and never had a problem with it.

    What I'm trying to do now is count all the units of a particular type (eg 1 bedrooms) which are vacant. So I try =COUNTIFS(A:A,"1 br",B:B,CountCol(B:B,1)). However, this returns a 0 when it should be returning a 1. I think COUNTIFS() should work because if I put a value in the black cells (say 1) and use the formula =COUNTIFS(A:A,"1 br",B:B,1) it returns 1, which is correct.

    I've attached the spreadsheet to this message and I'm really hoping someone can help me out with this. I've spent hours trying to google this and I just have on idea what I'm doing wrong. The logic seems to make sense so maybe it's something about the CountCol custom formula that isn't allowing it to work.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Countifs to compare two columns and return the total number of colored cells

    hi gogi, welcome to ExcelForum, I've added one optional argument to your custom function - br. Examples of function usage are in H9:H11. Please check attachment
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-20-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Countifs to compare two columns and return the total number of colored cells

    Hi watersev. Thanks for the help with this, that does the trick. However how could I make it so I can select the range of cells it looks for the optional argument "br". I have a lot of these spreadsheets and sometimes the 'unit type' is located in different columns, so it would be helpful if I could do something like =CountCol(B1:B37,3,Z:1:Z37,"1 br") and it would look for the "1 br" in the Z column.

    Thanks!

  4. #4
    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: Countifs to compare two columns and return the total number of colored cells

    you could put your search criteria in a separate cell, and then just reference that cell in waterserv's formula? so instead of using "1 br", it would reference A1, which contains 1 br
    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

  5. #5
    Registered User
    Join Date
    09-20-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Countifs to compare two columns and return the total number of colored cells

    Hi FDibbins - I figured I could reference a separate cell for search criteria. What I mean is that the way waterserv changed the formula is that it looks at the cell right before. My VB knowledge is next to nill but he added this: If Cell.Offset(, -1). so what I believe that does is looks at the cell right before, correct? So this works if I have Unit Type in Column A and Color in Unit B.

    But sometimes what I'll have is Unit Type in Column A, a few other columns of information, and then the colors in Column E, for example. So how can I tweak the code that waterserv made to have it look at a range I specify.

    This is the entire code:
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Countifs to compare two columns and return the total number of colored cells

    please check attachment, I've added optional function argument "br_range". Examples of function usage are in I7:I11.
    I7 shows error because SumRange (C1:C37) and br_range(A1:A30) are not equal in number of cells
    I8 shows error because function has range reference for the whole column and that is prohibited by the code to avoid unecessary calcultaions for "quick" references like C:C when the actual data range is C1:C200
    Attached Files Attached Files
    Last edited by watersev; 09-22-2012 at 09:59 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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