Previous Topic Next Topic

Advanced if

Closed
Nate - Dec 2, 2009 at 10:17 AM
 Trowa - Dec 3, 2009 at 09:09 AM
Hello,

I have a two part question. If i do an if statement in excel and i want it to pull multiple cells and show them if the statement is true how would i do that. Plus if i wanted to take one cell and make the statement true if it matches any cell within one column what would i write
Related:

2 responses

Answer 1 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 2, 2009 at 08:27 PM
for your second question

let me rephrase it
suppose there are numbers in the rows 2 to 10 both in column A and B.
if you want to find out whether the numbers (or any entries) in B2 is available in A2 to A10,

then in C 2 copy this formula 

=IF(ISNUMBER(MATCH(B2,$A$2:$A$10,0)),"yes","no")


copy C2 down.

extend rows to suit you
0
Answer 2 / 2
Trowa
Dec 3, 2009 at 09:09 AM
For your first question you will need an array formula.

You didn't give any specifics, so let me give you an example.

If you want to know how many times the word "yes" appears in range A1:A10 use this formula:
=SUM(IF(A1:A10="yes",1,0) confirm by hitting Ctrl+Shift+Enter.

By hitting Ctrl+Shift+Enter you will make the formula an array formula. This checks the condition for the entire range.
Lets's say A2, A5 and A8 has the word "yes". The formula works like this:
The IF function checks if each cell in range A1:A10 = yes and results in true or false.
=SUM(IF(False, True, False, False, True, False, False, True, False, False, 1, 0)
Now the IF function replaces true by 1 and false by 0.
=SUM(0, 1, 0, 0, 1, 0, 0, 1, 0, 0)
Finally the SUM function adds up the numbers.
=3

Now you know that the condition A1:A10 = yes is true three times.

Best regards,
Trowa
0