How can I count cells with numbers?

[John null]

Hi,

I have a range of data that are formatted and wanted to know if there is a way to find the frequency/count for specific formatted ( by font or by fill color) cells along with the number inside the cell. Please see the excel sheet attached. I tried using a color-count VBA code but I am having trouble creating a condition where two criteria have to be met; 1) font/fill color 2) the number(s) (inside the cell) before beginning to count.  Overall, I am trying to compute the frequency of these numbers that are conditionally formatted. Any help will be much appreciated.

[#2]

Hi John,

Perhaps you may use you conditional formatting rule formula

=SUMPRODUCT(--($A$1:$E$325=OFFSET($A$1:$E$325,1,0)))

to count cells under the rule and

=SUMPRODUCT(--($A$1:$E$325=OFFSET($A$1:$E$325,1,0))*(($A$1:$E$325=I3)))

for frequency

[#3]

Hi Sergei,

The formula below worked great! Unfortunately, I cannot get it to work with a different conditionally formatted cells. I am now trying to find the frequency on numbers and cannot find a solution. Please see the excel attached. I will appreciate any help. 

[#4]

Hi John,

Since your conditional formatting rule formula is

=A1+1=B1

the formula to calculate such pairs will be

=SUMPRODUCT(($A$1:$E$108+1=OFFSET($A$1:$E$108,0,1))*(($A$1:$E$108=0+LEFT(H2,SEARCH(",",H2)-1))))

First multiplier emulates your conditional formatting rule formula. Second one selects concrete pair from your list. 0+ is needed to convert text returned by LEFT to number (could be VALUE())

Attached.

[#5]

Hi Sergei,

I've tried the formula below and it works great for numbers but I am now wondering if it will work the same for other numbers? Please see the attachment.

[#6]

Hi John,

That will be exactly the same, just add one more AND condition (in red)

=SUMPRODUCT(($A$1:$E$108+1=OFFSET($A$1:$E$108,0,1))*($A$1:$E$108+2=OFFSET($A$1:$E$108,0,2))*(($A$1:$E$108=0+LEFT(H2,SEARCH(",",H2)-1))))

[#7]

Hi Sergei,

I am wondering how can I modify the below equation:

=SUMPRODUCT(--($A$1:$E$325=OFFSET($A$1:$E$325,1,0))*(($A$1:$E$325=I3)))

To make it count numbers that have been recycled in the previous row (current formula above only counts the below number but fails to count a recycled number that is not under it). I've managed to complete a conditional format that will highlight them in red but I cannot figure out on how to count the actual number of times the number was recycled given a data set. Please see the attached file.

[#8]

Hi John,

To clarify - you count the numbers which are in next row in the same and next columns (34, 24 on screenshot), but not in previous columns (28), correct?

[#9]

Hi Sergie,

I want to count any numbers that are in a given row (e.g., 28,39,20,34,14) that recycle into the next above row (e.g., 7,17,28,34,8) and to be able to plot the frequency of occurrence:

Numbers:        Freq:

28                    1

34                    1

7                      0

17                    0

[#10]

Hi John,

I changed conditional formatting rule formula on

=COUNTIF(OFFSET(A2,-1,1-COLUMN(A2),,5),A2)

which applied starting from row #2, and the formula to calculate frequency

=SUMPRODUCT(--(MMULT(--($A$2:$E$325=$I3)+--(OFFSET($A$2:$E$325,-1,0)=$I3),{1;1;1;1;1})>1))