CODEWITHSUNDEEP
excelexcel-formulacountif
footbalbum99
footbalbum99

Reputation: 13

How do you add multiple countif statements in Excel?

My current formula will just count any dup as "2X" but if it has 3 dups I would like to see "3X" and "4X" for 4 dups and so on. How can I add on to my formula?

=IF(COUNTIF($A$2:$A$14736, $A2)>1, "2X", "")

Upvotes: 1

Views: 75

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Give this a try:

=IF(COUNTIF($A$2:$A$14736, $A2)<2,"",COUNTIF($A$2:$A$14736, $A2)-1 & "X")

enter image description here

In the above example we have an original value and two duplicates.
If there are no duplicates (only the original), then the formula leaves the cell blank.
(if you want to count the total number of items (original + duplicates), then just drop the -1 in the formula)

Upvotes: 1

Dominique
Dominique

Reputation: 17493

So you are doing this:

Count the number of occurences
If the count is larger than 1, print "2X"

Why don't you do this:

Count the number of occurences
Concatenate that number with "X"

Upvotes: 2

Related Questions