How can i count the same value

[miguel65]

I have colum A with around 60 rows.the values are
3 x 4
3 x 5
4 x 3
4 x 4
4 x 5
5 x 3
5 x 4
5 x 5

and I have used conditional formating on excel 2007 but i would like to create a VBA macro for it.

when I count the repetitive values through out rows 2 to 61 I get the tally of

3 x 4 =2
3 x 5 =7
4 x 3 = 15
4 x 4 = 20
4 x 5 = 10
5 x 3 = 1
5 x 4= 3
5 x 5 = 2

should I use CASE OF.....
or something else?

thanks

On this one I do not how to start ...can anyone point me in the write direction, thanks

 

[GlennUK]

I don't see how counting occurences has anything to do with conditional formatting ... care to explain a little further?

 

[miguel65]

I don't see how counting occurences has anything to do with conditional formatting ... care to explain a little further?

Do not worry about conditional formating, as for the example I had to practice with was a quickfix:
So what I did to make this count was:
1)I manually counted the instances, then sum them up.
2)used conditional formating on office2007 to help me to see automatically with colored cells how many of the same colored cells will show up so that I made sure I did not do any mistakes by counting them manually boring job !!
So in a more practical way:
As what I would like to have is a macro where I just run it and would gice me the number of each instances of ?x?
and for this particular example they have to add 60. Cause I have 60 rows.
Does that make sense?

Though I still need to use those 8 combinations (of ?x?) as specified above. :-0

 

[mikerickson]

Try this formula (in cell D1 below)
=SUMPRODUCT(--((LEFT(dataRange,1)*RIGHT(dataRange,1))=(LEFT(C1,1)*RIGHT(C1,1))))

it should return the number of entries whose product equals the product of the value in C1.

DataRange is a named range (a1:A3 in below). I used a dynamic named range, because the formula will error if any cells are blank.
<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>3X4<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>2X6<td align="right" bgcolor=#FFFFFF>2</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>4X3<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF>3X3<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>

 

[jeffbecool]

There are a couple of ways to do this. I would not recommend a macro, mostly because I am not familiar. I would suggest using SUMIF. You would first copy the unique records to an area you want to summarize the instances. This can be done by going to Data / Filter / Advanced Filter and "copying unique records only" to another location. Then you can write your SUMIF formula from there.

 

[GlennUK]

Do not worry about conditional formating, as for the example I had to practice with was a quickfix:
So what I did to make this count was:
1)I manually counted the instances, then sum them up.
2)used conditional formating on office2007 to help me to see automatically with colored cells how many of the same colored cells will show up so that I made sure I did not do any mistakes by counting them manually boring job !!
So in a more practical way:
As what I would like to have is a macro where I just run it and would gice me the number of each instances of ?x?
and for this particular example they have to add 60. Cause I have 60 rows.
Does that make sense?

Though I still need to use those 8 combinations (of ?x?) as specified above. :-0

If you simply want to count occurences of each, then just create a pivottable. Have a heading for your list. Have the single field in the row label area and also in the values data area, and it'll count for you.