Searching two columns for information

C

Colin

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.
 
P

Peo Sjoblom

=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
 
N

N Harkawat

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

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

Colin

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

Colin.


Peo Sjoblom said:
=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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Searching two columns against a specific criteria 2
Return a value if a range of cells contain text 2
Lock Unlock Cell Protection 3
Search for specific text within last non blank cell in a range 2
Conditional Formatting 3
copy specific cells from sheets if the information is correct 3
Averaging two ranges on the same column (excluding zeros) 2
Handle a value from a Range instead of value of a Cell 8

Top