Countif based on a Conditional Format??

[Im_Offset]

Is it possible to countif, sumif, whateverif based on a cell's format (conditional format)?

I realize I could just make the formula based on whatever the conditional format is...but I am unaware of how to sum how many times a value is repeated in a list!! So, if I can't sumif based on a conditional format, will someone tell me how I can analyze a list and tell me how many duplicates there are???

Thank you!!

 

[Chihiro]

You can't use CF or cell formatting in formula. It can be done via VBA but it's often cumbersome to do so.

Best bet is to use the condition used in CF as basis of your calculation.

Ex: ID in Col A, Value in Col B. 50 rows of data & 1 row of header.
To apply CF for Top 10 values, formula is going to be...
=$B2>LARGE($B$2:$B$51,11)

So SUMIF formula becomes...
=SUMIF(B2:B51,">"&LARGE($B$2:$B$51,11))

See attached.

Alternately, you can convert data to table and add Total Row and filter the table based on cell colour as well.

 

[Chihiro]

Woops I missed last bit on your post... to add/count duplicate, easiest method is through PivotTable.

As for formula method. Depends on what you mean exactly "how many duplicates".

1. Is it Count of values that have duplicates
2. Count of all duplicates.

Ex:
Array = 10, 11, 10, 12, 13, 14, 10, 11, 15

1. Will be 2 as 10 & 11 has duplicates
2. 5 as there's 3x 10 & 2x 11

Or is it some other count of duplicate?

 

[Im_Offset]

Woops I missed last bit on your post... to add/count duplicate, easiest method is through PivotTable.

As for formula method. Depends on what you mean exactly "how many duplicates".

1. Is it Count of values that have duplicates
2. Count of all duplicates.

Ex:
Array = 10, 11, 10, 12, 13, 14, 10, 11, 15

1. Will be 2 as 10 & 11 has duplicates
2. 5 as there's 3x 10 & 2x 11

Or is it some other count of duplicate?

Sorry for the delayed response. #1 is what I would want. there are two sets of duplicate numbers.
Thanks!

 

[Chihiro]

Formula would be something like
=SUM(IF(FREQUENCY(B2:B51,B2:B51)>1,1))

See attached.

 

[Im_Offset]

Formula would be something like
=SUM(IF(FREQUENCY(B2:B51,B2:B51)>1,1))

See attached.

Awesome! Thank you...I learn so much here)