Forum Discussion
How can I count cells with numbers?
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
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.
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?
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
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.
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.
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.
