+ Reply to Thread
Results 1 to 6 of 6

Check if a number of cells are equal

  1. #1
    Registered User
    Join Date
    11-28-2008
    Location
    Sweden
    Posts
    9

    Check if a number of cells are equal

    Hello,

    I would like to know if there is any function in excel to check if a number of cells in a column are equal? For example, check if A5:A20 are equal. Note, that I do not want to check if all cells in the column are equal, rather just a range of cells.

    I would like this function to accept any content in the cell, i.e. both numbers and strings. I did my own check based on standard deviation (i.e. =IF(STDEV(A5:A20)=0;1;0)) but of course this does not work for strings...

    Anyone who knows how to solve this?

    Thanks in advance!
    Best Regards,
    Samuel

  2. #2
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re: Check if a number of cells are equal

    Do you mean that

    IF A5:A20 has the same content regardless the TYPE?

    Then

    =IF(COUNTIF(A5:A20,A5:A20)=COUNTA(A5:A20),1,0)

    Or as =SUMPRODUCT(1/COUNTIF(A5:A20,A5:A20)) defines the number of UNIQUE numbers (Which must be one)

    =IF(SUMPRODUCT(1/COUNTIF(A5:A20,A5:A20))=1,1,0)
    Last edited by rwgrietveld; 12-18-2008 at 09:16 AM.
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    One way. With your data in say A1:A5, enter in B1:

    Please Login or Register  to view this content.
    and copy down to B5.

    Now in C1 enter:
    Please Login or Register  to view this content.
    Adjust ranges to taste.

    HTH

  4. #4
    Registered User
    Join Date
    11-28-2008
    Location
    Sweden
    Posts
    9
    Quote Originally Posted by rwgrietveld View Post
    Do you mean that

    IF A5:A20 has the same content regardless the TYPE?

    Then

    =IF(COUNTIF(A5:A20,A5:A20)=COUNTA(A5:A20),1,0)

    Or as =SUMPRODUCT(1/COUNTIF(A5:A20,A5:A20)) defines the number of UNIQUE numbers (Which must be one)

    =IF(SUMPRODUCT(1/COUNTIF(A5:A20,A5:A20))=1,1,0)
    Thanks for your answers! I tried SUMPRODUCT and it worked fine. However, I realized that I want the function to not care about empty cells. That is, I want to include a large data range which most often will not be filled with data. Therefore I want the function to only check if the cells that contain data in the data range are equal. Sorry that I didn't mention this in my original question.

    Also, I would prefer if the function was possible to write as a one-liner since I am going to use it to control conditional formatting.

    Thank you.

  5. #5
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671
    Then I would use:
    =IF(SUMPRODUCT(COUNTIF(A5:A20,A5:A20)/COUNTA(A5:A20))=COUNTA(A5:A20),1,0)

  6. #6
    Registered User
    Join Date
    11-28-2008
    Location
    Sweden
    Posts
    9
    Hello and thanks for your answer,

    This seems to work fine except for the case when the content of the cells equal zero ("0"). Is there any way to include this case also?

    Thank you.

+ 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