Count how many times a text group appears in a range

[Osvaldo Palmeiro]

Please, I would like to have a formula to calculate how many times the group of words in D1: D3 appears, in that order, in the range A1: A20.
The formula should return 4.
Thanks in advance.

<tbody>
[TD="class: xl68"]A[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl68, width: 64"]D[/TD]

[TD="class: xl66"]cat[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"]cat[/TD]

[TD="class: xl66"]dog[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"]dog[/TD]

[TD="class: xl66"]turtle[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"]turtle[/TD]

[TD="class: xl67"]aaa[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"]kkk[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]cat[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]dog[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]turtle[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"]xxx[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]cat[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]dog[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]turtle[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]cat[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]dog[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]turtle[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"]aaa[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"]dog[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"]cat[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"]xxx[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"]turtle[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

</tbody>

 

[Logit]

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]cat[/td][td][/td][td][/td][td]cat[/td][td]5[/td][td]=COUNTIF(A1:A20,D1)[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]dog[/td][td][/td][td][/td][td]dog[/td][td]5[/td][td]=COUNTIF(A2:A21,D2)[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]turtle[/td][td][/td][td][/td][td]turtle[/td][td]5[/td][td]=COUNTIF(A3:A22,D3)[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]aaa[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]kkk[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]cat[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]dog[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]turtle[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]xxx[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]cat[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]dog[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]turtle[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]cat[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]dog[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]turtle[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td]aaa[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td]dog[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

18​

[/td][td]cat[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

19​

[/td][td]xxx[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

20​

[/td][td]turtle[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[steve the fish]

This should do it:

=COUNTIFS(A1:A20,D1,A2:A21,D2,A3:A22,D3)

 

[Osvaldo Palmeiro]

Hi, Logit. Thanks for reply, but I was hopping to a count for the group of texts located in D1: D3, and not individual counting for each text separately.

Hi, Steve. Thanks for the solution. It worked as expected.
As I've posted just a reduced sample, so the range in A could be A1: A150 and the searched range could be D1: D10, for example, would there be some other formula, maybe an array one, that could reference the ranges directly? Something like SUMPRODUCT (A1: A150, D1: D10).
Thanks any way.