Advanced if

Closed
Report
-
 Trowa -
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

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
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