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