Searching two columns for information
 

I have a range of data in A2:G30 with cells in range A2:A30 with 'yes' or
'no' as text in the cells, and I have 'David', 'Andrew', 'Charles' or 'John'
as text in cells D2:D30.
I want to put in cell K50 a function that searches D2:D30 for 'Charles',
then records the number of cells in the corresponding rows in range A2:A30
where the cells are 'yes'.
How do I do this please.
--
Thank you,

Colin.

Searching two columns for information
 

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

replace the hardcoded criteria with cells for better editing

=SUMPRODUCT(--(A2:A30=H2),--(D2:D30=I2))


and then type the criteria into H2 and I2, that way you don't have to edit
the formula when you change criteria


--
Regards,

Peo Sjoblom



"Colin" wrote in message
...
I have a range of data in A2:G30 with cells in range A2:A30 with 'yes' or
'no' as text in the cells, and I have 'David', 'Andrew', 'Charles' or
'John'
as text in cells D2:D30.
I want to put in cell K50 a function that searches D2:D30 for 'Charles',
then records the number of cells in the corresponding rows in range A2:A30
where the cells are 'yes'.
How do I do this please.
--
Thank you,

Colin.

Searching two columns for information
 

=sumproduct(--(a2:a30="yes"),--(d2:d30="Charles"))

will give you the count of all "yes" with "charles"

"Colin" wrote:

I have a range of data in A2:G30 with cells in range A2:A30 with 'yes' or
'no' as text in the cells, and I have 'David', 'Andrew', 'Charles' or 'John'
as text in cells D2:D30.
I want to put in cell K50 a function that searches D2:D30 for 'Charles',
then records the number of cells in the corresponding rows in range A2:A30
where the cells are 'yes'.
How do I do this please.
--
Thank you,

Colin.

Searching two columns for information
 

So simple when you know how - many thanks.
--
Thank you,

Colin.


"Peo Sjoblom" wrote:

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

replace the hardcoded criteria with cells for better editing

=SUMPRODUCT(--(A2:A30=H2),--(D2:D30=I2))


and then type the criteria into H2 and I2, that way you don't have to edit
the formula when you change criteria


--
Regards,

Peo Sjoblom



"Colin" wrote in message
...
I have a range of data in A2:G30 with cells in range A2:A30 with 'yes' or
'no' as text in the cells, and I have 'David', 'Andrew', 'Charles' or
'John'
as text in cells D2:D30.
I want to put in cell K50 a function that searches D2:D30 for 'Charles',
then records the number of cells in the corresponding rows in range A2:A30
where the cells are 'yes'.
How do I do this please.
--
Thank you,

Colin.