[HELP] How to count each one ONLY once ?

[Mr.Nob0OOdy]

I have the following Excel table:
vr59h2.png

I want to see how many numbers (not how many times) from the first row appear in the last two rows.

I tried to sum each countif for each number from the first row in the range area of these last two rows.
It doesn't work.

If I try to countif for 40, as you see in image, it shows to me 2 because it appears two times: one on the row and the other on the last row. It should say to me only 1.
Then if I try to do the same for every number in the first row it should give to me for each one of them 0 or 1. Just 0 or 1.
Then, finally added all together in order to find out how many numbers (not how many times) from the first row appear in the last two rows.

Help me, please.

Thank you so much !

[FDibbins]

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

[Spitzerpl]

If you only have 5 columns you could use a string of if statements

Please Login or Register  to view this content.

[jhren]

Yeah, a sample file will help, and could you elaborate... is there only going to be three rows? Your image shows three rows, but it also appears to be a Table. A Table can be sorted and/or filtered. The last two rows being displayed may not be the last two rows in displayed when filtered. Sorting may change the first and last two rows. And then there's the question whether the result of this determination is going to be in the table or outside the table. Then there's also the question whether the data in the table is static or dynamic (i.e. brought into the table by formula referencing data outside the table.

Need mo' info'

[Mr.Nob0OOdy]

If you only have 5 columns you could use a string of if statements

Please Login or Register  to view this content.

Thanks !
Stars added. :D

[Spitzerpl]

Your welcome.

[Tony Valko]

Not real clear.

If you want to know how many ROWS contain at least one matching number from the first row...

With your data in the range B2:G4...

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(B3:G4,B2:G2,0)),{1;1;1;1;1;1})>0))