Forum Discussion
If COUNTIF Help
I'm using the following formula in column I to provide me with a value ("1") for every unique instance in column G. The formula actually works great, except for one thing. In cell I88 it gives me a value of 1, with no other cells beyond with the same. Ultimately, I end up with an additional 1 in column I (Cell I88) to count than I need (my total range is I7:I1000, and G7:G1000 respectively, and the formula in Column I below should stop producing a value, or 1, at I87). I'm guessing the formula is seeing either a text value or numeric value in column G as it contains an "=LEFT" formula all of the way down the entire column, but with no actual value showing. Any help given is greatly appreciated.
'=IF(COUNTIF($G$7:$G$1000,G87)>1,IF(COUNTIF($G$7:G87,G87)=1,"1",""),IF(COUNTIF($G$7:$G$1000,G87)=1,IF(COUNTIF($G$7:G87,G87)=1,"1","")))
Kelly,
check LEN(G88).
If it is >0 then there is something in the cell.
Hi Kelly,
Sorry, but your explanation of the issue isn't clear enough!
Anyway, I have these two formulas for you which could help you:
Use this if you want to hide all the duplicate values including the first instance:
=IF(COUNTIF($G$7:$G$1000,G7)=1,1,"")
And this if you want to hide all the duplicate values excluding the first instance:
=IF(COUNTIF($G$7:G7,G7)=1,1,"")
My apologies - please see below, and attached, for a better explanation of my issue:
- I have a spreadsheet with data in it starting in Cell A7 and finishing in Cell M1000. However, for the purpose of my formula, I am only utilizing Columns A, G, and I:
- Column A (unique 10 digit values always starting with an H in Cell A7; the length will always be different, but no longer than cell A1000). Example values are H123456789, H987654321, H543219876, and H987612345.
- Column G (extract with unique and duplicate matching values always starting with an H in Cell G7) that has to be compared against column A, cells A7 – A1000. Examples of the values are H123456789, H123456789, H123456789, H543219876, and H987612345.
- Column I, which contains the actual formula starting in cell I7 and runs through the end of extract (again, never over 1000 rows long). An example of the formula from Cell I87 is:
=IF(COUNTIF($G$7:$G$1000,G87)>1,IF(COUNTIF($G$7:G87,G87)=1,"1",""),IF(COUNTIF($G$7:$G$1000,G87)=1,IF(COUNTIF($G$7:G87,G87)=1,"1","")))
- For every unique or duplicate instance in Column G (i.e. H123456789, H123456789, H123456789, H543219876, H987612345), that matches a unique value in Column A, I would like to assign a “1” to it in Column I. If Column G contains blanks, zeros, or non-matching value against Column A, I would like to show a null value in Column I (with “”).
- When applying this formula in Column I (I7 through I1000), it works correctly every time. However, wherever my last matching value is in Column I, the very next cell in Column I shows a “1” as well, when it should, in fact, show a null value. Ultimately, whatever the right answer is, it always comes up 1 more than the anticipated answer because of the extra (1 after the last match).
- I suspect the extra “1” after the last match in Column I is because for every cell in the Column G range (G7 - G1000) there is an =LEFT(FXX,10) formula contained within, which truncates values originating in Column F and places them in Column G to start with. When you look at Column G you don’t see the formula (range G7 through G1000), although I believe Excel does as a text value because of the =LEFT formula contained within.
Ultimately, no matter where my formula stops in Column I, I would like to eliminate the extra “1” in the following cell. An example would be if my last matching value is in Cell G87, I would like to get rid of the extra (erroneous) value of “1” in Cell I88.
Again, thank you.
Kelly,
G88 houses the first instance of the null value thus calculating a 1.
Why don't you use Haythams suggestion with a little extra:
=IF(AND(G7<>"",COUNTIF(G$7:G7,G7)=1),1,"")
As Haytham and Detlef suggested, I will use something like
=IF(AND(LEN(G7)>0,<Condition>), 1, "")
In addition, if you do not care about the actual count of the occurances, but just the first occurance, you may try to compare the current row with the matching row in column G (the row of first occurance should be less than the current row).
=IFERROR(IF(AND(LEN(G7)>0,MATCH(G7,$G$7:$G7,0)+6=ROW(G7)),1,""), "")
If you have 1000 rows, I think using MATCH function will have better performance than using COUNTIF function.
Thank you Willy, Haytham, and Detlef, I greatly appreciate all of your assistance and time.
- I have a spreadsheet with data in it starting in Cell A7 and finishing in Cell M1000. However, for the purpose of my formula, I am only utilizing Columns A, G, and I:
