Searching two columns against a specific criteria

[Colin]

Hi,
I have a range of data in A2:G30.
The cells in range A2:A30 have either 'yes' or 'no' as text in the cells.
The cells in range D230 have either 'David', 'Andrew', 'Charles' or 'John'
as text in cells.
I want to put in cell K50 a function that returns the number of times that
'Charles' is mentioned in column D and where 'yes' is in the same row and
mentioned in column A.
I have tried 'sumproduct' but this only returns '0' and according to Excel
help only works with numerical data in the cells.

Please can anyone help.

Many thanks

 

[Toppers]

SUMPRODUCT will work:

=SUMPRODUCT(--(D230="Charles"),--(A2:A30="yes"))

It's better to put the parameters in cells and use:

=SUMPRODUCT(--(d2:d30=X1),--(A2:A30=x2))

x1="Charles", X2="Yes" (text)

the -- converts TRUE/FALSE to 1/0 so SUMPRODUCT can do the required
arithmetic.

P.S. In future, please post your formula(e) if you have problems.

HTH

 

[Colin]

Hi,

Big thank you for this. My main learning curve was understanding the
relevance of the dashes in the function. I'll take on board your comments
about the posting of the actual function next too.