Counting the Number of times text appears by criteria

[William53]

Hi All
My issue is this:
I have a large data set which unfortunately I cannot show which has multiple columns. I want to find certain text in a column of data (the cells can contain anything from 5 -20 words) but only count that text when the adjacent cell contains another word. I have found a lot of articles/formulas (sumproduct etc) that will count the number of times it appears but also counts the adjacent cell as well so instead of getting 10 as the answer it is giving me 20. An example is below.

Overtopping	Water from Pad 2 has overtopped the basin and needs emptying
Water	Excess water has been captured in the rainwater pit at Pad 2
Overtopping	Water from the basin at Pad 2 has overtopped

What I need the formula to do is count the number of times Pad 2 occurs when the adjacent cell says Overtopping, so in this case it would be 2

Many thanks for any assistance

William

 

[HongRu]

Try.

活頁簿1
	A	B	C	D
1	Overtopping	Water from Pad 2 has overtopped the basin and needs emptying		Count
2	Water	Excess water has been captured in the rainwater pit at Pad 2		2
3	Overtopping	Water from the basin at Pad 2 has overtopped
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUM((A1:A3="Overtopping")*(ISNUMBER(FIND("Pad 2",B1:B3))))

 

[William53]

Try.

活頁簿1
	A	B	C	D
1	Overtopping	Water from Pad 2 has overtopped the basin and needs emptying		Count
2	Water	Excess water has been captured in the rainwater pit at Pad 2		2
3	Overtopping	Water from the basin at Pad 2 has overtopped
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUM((A1:A3="Overtopping")*(ISNUMBER(FIND("Pad 2",B1:B3))))

Thank you so much HongRu, works perfectly.

 

[Peter_SSs]

COUNTIFS is designed exactly for this sort of task.

25 03 22.xlsm
	A	B	C	D
1	Overtopping	Water from Pad 2 has overtopped the basin and needs emptying		Count
2	Water	Excess water has been captured in the rainwater pit at Pad 2		2
3	Overtopping	Water from the basin at Pad 2 has overtopped
COUNTIFS

Cell Formulas
Range	Formula
D2	D2	=COUNTIFS(A1:A3,"Overtopping",B1:B3,"*Pad 2*")