Countif?

[JTL9161]

(I am simplifying the data to make explaining easier)

I have a table G60:G75. Some with data some with blanks. I then have H60:H75 all with data. I need the sum of the number of times the data in G60:G75 matches the data in H60:H75.

1 5
2 2
3 3
4
4 5

So for the above table the answer I am looking for is 2 as the 2 in G61 = H61 and the 3 in G62 = H62.

Any help will be appreciated
James

 

[JTL9161]

Ok last time...I hope

I am trying to show the 2 numbers formatted like this 6 - 9 showing how many match first (6) and how many do not (9). I used this
=SUMPRODUCT(--($G$60:$G$74=$H$60:$H$74))&" - "&SUMPRODUCT(--($G$60:$G$74<>$H$60:$H$74)) Now I need to add the *(G60:G75<>"")) so not count the blanks if any. Where do I put that part of the formula. I've tried several different ways and keep getting an error.

 

[Fluff]

You've totally lost me, can you please post some data with the expected results?

 

[Special-K99]

Yep as Fluff said error with brackets, my bad.

 

[JTL9161]

Sorry. I'm no good explaining in type.

The first result is when data in column G matches data in column H. So for example "6" with this formula =SUMPRODUCT(--($G$60:$G$74=$H$60:$H$74)) then I want the cells that do not match G versus H so that would be "9" with this formula SUMPRODUCT(--($G$60:$G$74<>$H$60:$H$74)) to put a dash between them I did used &" - "$ and then got the result of "6 - 9" , but I need to add another formula that DOES NOT match blank cells in G with blank in H. With that I use
*($G60:$G74<>"") so now I need to tie all 3 formula's together while ending with the "6 - 9" formatted answer.

Hope that is better.
Thanks again.

 

[Fluff]

Can you please post some sample data, showing you expected results?

 

[JTL9161]

Here is my example table

<colgroup><col style="mso-width-source:userset;mso-width-alt:3254;width:67pt" width="89"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> </colgroup><tbody>
[TD="class: xl73, width: 89"]3 - 12[/TD]
[TD="class: xl72, width: 81"] [/TD]

[TD="class: xl68"]5[/TD]
[TD="class: xl69"] [/TD]

[TD="class: xl66"]6[/TD]
[TD="class: xl70"]1[/TD]

[TD="class: xl66"]8[/TD]
[TD="class: xl70"]5[/TD]

[TD="class: xl66"]9[/TD]
[TD="class: xl70"]8[/TD]

[TD="class: xl66"]2[/TD]
[TD="class: xl71"]9[/TD]

[TD="class: xl66"]6[/TD]
[TD="class: xl70"]3[/TD]

[TD="class: xl66"]4[/TD]
[TD="class: xl70"]4[/TD]

[TD="class: xl66"]5[/TD]
[TD="class: xl70"]7[/TD]

[TD="class: xl66"]3[/TD]
[TD="class: xl70"]5[/TD]

[TD="class: xl66"]8[/TD]
[TD="class: xl70"]6[/TD]

[TD="class: xl66"]6[/TD]
[TD="class: xl71"]8[/TD]

[TD="class: xl66"] [/TD]
[TD="class: xl70"] [/TD]

[TD="class: xl66"]9[/TD]
[TD="class: xl70"]9[/TD]

[TD="class: xl66"]3[/TD]
[TD="class: xl70"]2[/TD]

[TD="class: xl67"]8
[/TD]
[TD="class: xl70"]1[/TD]

</tbody>

Note that the 3 in the "3 -12" means 3 cells in the 1st column match 3 cells in the 2nd column. The 12 means 12 do not. To come up with the "3 - 12" I use:
=SUMPRODUCT(--($G$60:$G$74=$H$60:$H$74))&" - "&SUMPRODUCT(--($G$60:$G$74<>$H$60:$H$74))

But, Notice the 4th row from the bottom (above the matching 9's). Both 1st and 2nd columns are blank but the above formula is counting it as the 3rd match when I only want 2 to show. So I am looking for "2 - 11" instead of "3 -12" To not read the blank cells I have the formula *(G60:G74<>"")) which does not add the matching blank cells to my totals. I am trying to figure out how to add the *(G60:G75<>"")) into the formula (1st formula) =SUMPRODUCT(--($G$60:$G$74=$H$60:$H$74))&" - "&SUMPRODUCT(--($G$60:$G$74<>$H$60:$H$74))

Hopefully that explains it.

<tbody>
[TD="width: 89"][/TD]

</tbody>

 

[Fluff]

Ok, how about
=SUMPRODUCT(($G$60:$G$74=$H$60:$H$74)*($G$60:$G$74<>""))&" - "&SUMPRODUCT(($G$60:$G$74<>$H$60:$H$74)*($H$60:$H$74<>"")*($G$60:$G$74<>""))

 

[JTL9161]

Bingo!

Thanks for you help!

 

[Fluff]

You're welcome & thanks for the feedback