Hello everyone,

I need to perform a multicriteria search in xcel with complicated parameters for a beginner. I want to search how many times "n" appears in column one and "PE" appears in column 2. I tried =SUM((A2:A6="n")*(B2:B6="*PE*")) but keep getting #VALUE! back. In this simple example, the total should be 1 as only the third line has "n" in column one with "PE" contained in the column two. Thanks for any assistance!y y, PE

n n

n y, PE

y y, PE, DVT

n y, DVT

I assume your data looks like this: A B 1 2 y y, PE 3 n n 4 n y, PE 5 y y, PE, DVT 6 n y, DVTIf so, try this:

=SUMPRODUCT((A2:A6="n")*NOT(ISERROR(SEARCH("PE",B2:B6))))

P.S. In the future, please follow the steps outlined in the How To referenced in my signature line before posting data.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History