Google Sheets formula match multiple criteria

Question

I have a spreadsheet like the one shown in the image. What I would like to do is generate a formula that will count how many times MALEs from NEW YORK choose the BLUE color. In column D, the user can enter 1-3 different colors. Column B will be ignored in this case. Can anyone help me with the formula?

Answer 1

Try this formula in Google docs

=ARRAYFORMULA(sum(if(A2:A4="Male",if(C2:C4="New York",If(isnumber(search("Blue",D2:D4)),1)))))

you can expand it to as many rows as you need