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

3 - 12
5
6	1
8	5
9	8
2	9
6	3
4	4
5	7
3	5
8	6
6	8
9	9
3	2
8	1

<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>
</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>
</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