StdevIF() or Stdev.SIF or Stdev.PIF?: How Perform Stdev only for rows that contain specific text in another column of that row?

Question

I have a large list of text in Column A, and numbers in Column B. How do I perform stdev on only the rows that contain "*test2*"

	A	B
1	text_bla-bla-bla6-test1-blablabla1.doc	5
2	text_bla-bla-bla7-test2-blablabla2.doc	15
3	text_bla-bla-bla8-test1-blablabla3.doc	4
4	text_bla-bla-bla9-test2-blablabla4.doc	17
5	text_bla-bla-bla10-test1-blablabla5.doc	6

etc

I essentially want stdev(15,17), where Excel fills this in based on what text is in Col A (without clicking on it). For just 2 it is easy to click on but if many rows, not so much.

Similar to the "sumif" function, If excel had the functionality directly I imagine it would probably look like this:

stdevIF(A1:A5,"*test2*",B1:B5)

???

Thanks!

PS.  Even when pressing CTRL+SHIFT+Enter, the following did not work for me:

=STDEV(IF(A1:A5="*test1*",B1:B5))

Answer 1

As an array formula, confirmed with Ctrl+Shift+Enter:

=STDEV(IF(ISNUMBER(SEARCH("test2",A1:A5)),B1:B5))

SEARCH is case-insensitive, so the above formula will include rows with "test2", "TEST2", "Test2" etc.

If you want only "test2", replace SEARCH with FIND.

Answer 2

HansV, 

Thank you so much for the fast, accurate and VERY helpful reply!  That worked beautifully.  I appreciated it all the more considering I spent hours trying to figure this out before I posted the question!  It would be nice if Excel added a function definition for this, but at least it works now!

Thank you again!

As an array formula, confirmed with Ctrl+Shift+Enter:

=STDEV(IF(ISNUMBER(SEARCH("test2",A1:A5)),B1:B5))