Advanced if Closed

Question

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

venkat1926 · Answer

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

Trowa · Answer

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

Advanced if

2 responses

Similar discussions

Subscribe To Our Newsletter!