count nos matches for series

[cds]

I have data something like this :

a b c d e f g h
12/12/2016 6.00 12 24 29 35 37 49
12/12/2016 9.00 4 8 17 29 37 43
13/12/2016 6.00 1 8 24 29 43 48


I want to count how many repeated in 13/12/2016 6.00 are from 12/12/2016 & 9.00 and 12/12/2016 6.00 seperately and together. I want result is something like

12/12/2016 9.00 separate = 3 ( 8,29,43)
12/12/2016 6.00 separate = 2 (24,29)
12/12/2016 9.00 & 6.00 together (ignoring repeats) = 4 (8,24,29,43)
while counting the nos matching together if it matching 12/12/2016 9.00 I want to create count last repeat count and previous to last repeat (ignoring repeat ) separately and result would be

last repeat count = 3 (8,29,43)
previous to last = 1 ( 24)


kindly guide me

 

[Aladin Akyurek]

Are 12/12/2016 6.00 together a single entry in a cell or separate entries?

 

[cds]

Are 12/12/2016 6.00 together a single entry in a cell or separate entries?

Thanks for your time, basically separate entries

 

[Aladin Akyurek]

Thanks for your time, basically separate entries

I don't understand why you add the qualification "basically".

12/12/2016	6	12	24	29	35	37	49
12/12/2016	9	4	8	17	29	37	43
13/12/2016	6	1	8	24	29	43	48

<tbody>
</tbody>

1. Just enter:

=SUMPRODUCT(COUNTIFS($C$3:$H$3,C1:H1))

2. Just enter:

=SUMPRODUCT(COUNTIFS($C$3:$H$3,C2:H2))

3. Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(C3:H3,C1:H1,0))+ISNUMBER(MATCH(C3:H3,C2:H2,0)),C3:H3),C3:H3),1))

 

[seekerarcane]

1. Formula For 12/12/2016 6.00 separate;

=SUMPRODUCT(COUNTIF(C1:H1,C3:H3))

2. Formula for 12/12/2016 9.00 separate;

=SUMPRODUCT(COUNTIF(B2:H2,B3:H3))

3. Formula for 12/12/2016 9.00 & 6.00 together

(Its an array formula, press CRT+SHIFT+ENTER in the formula cell, Not just ENTER)

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(C3:H3,C1:H1,0))+ISNUMBER(MATCH(C3:H3,C2:H2,0)),C3:H3),C3:H3),1))

Thanks

 

[Aladin Akyurek]

@seekerarcane

what is your point? Replacing COUNTIFS with COUNTIF?