Searching two columns against a specific criteria
 

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 D2:D30 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
--
Thank you,

Colin.

Searching two columns against a specific criteria
 

SUMPRODUCT will work:

=SUMPRODUCT(--(D2:D30="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" wrote:

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 D2:D30 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
--
Thank you,

Colin.

Searching two columns against a specific criteria
 

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.
--
Thank you,

Colin.


"Toppers" wrote:

SUMPRODUCT will work:

=SUMPRODUCT(--(D2:D30="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" wrote:

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 D2:D30 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
--
Thank you,

Colin.