Help with an array that requires "and" + "or"

[Rowey]

Hi, I am trying to create an array formula that does the following:

Counts the number of times "Expression of wish" appears in 1 column and then "p" or "f" appears in another.

So if I get Expression of wish appearing and P it will count it

If in another row Expression of wish appears and F appears it will also count it

This is as far as I have got, can anybody help?

=SUM((January!B2:B3="expression of wish")*(January!F2:F3="p"))

 

[schielrn]

Welcome to the Board!

Try:

=SUM((January!B2:B3="expression of wish")*((January!F2:F3="p")+(January!F2:F3="f")))

This needs to be entered with control+shift+enter and not just enter.

Hope that helps.

 

[njimack]

Welcome to the board.

=SUMPRODUCT(--(January!B2:B3="Expression of wish"),--(ISNUMBER(MATCH(January!F2:F3,{"p","f"},0))))

 

[Rowey]

Thank you thats great it has worked!

 

[scottylad2]

Would any instance of "Expression of wish" and P or F always occur on the same row? ie A1 and B1

Excel Workbook
	A	B	C
1			2
2
3	Expression of wish	F
4
5
6
7	Expression of wish	P
8
9
10
11
12
13	Expression of wish	z
Sheet3

Excel 2010

Cell Formulas
Range	Formula
C1	=COUNTIFS($A$1:$A$13,"Expression of wish",B1:B13,"p")+COUNTIFS($A$1:$A$13,"Expression of wish",B1:B13,"f")