# count nos matches for series

#### cds

##### Board Regular
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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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

#### cds

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

Thanks for your time, basically separate entries

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
@seekerarcane

what is your point? Replacing COUNTIFS with COUNTIF?

Replies
21
Views
623
Replies
0
Views
419
Replies
3
Views
710
Replies
3
Views
286
Replies
6
Views
730

1,190,874
Messages
5,983,345
Members
439,840
Latest member
billy1989

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back