IF Statement on multiple cells

[sunshine82]

Hi All,

I'm after a formula that will look at a number of cells(that are not in a range) with the word "C" and if they all = "C" then I want the cell to come back with the result "yes" otherwise "no".

Sorry sounds complicated I know!

Thank you,
S

[Ron Coderre]

Can you post a sample workbook that demonstrates what you want to see?

[ragulduy]

=if(countif(A1:A100,"=C")+countif(C4:C50,"=C")=counta(A1:A100,C4:C50),"yes","no")

?

[sunshine82]

Please find attached, thanks

[ragulduy]

maybe:
=IF(SUM(IF(ODD(COLUMN(A2:M2))=COLUMN(A2:M2),1,0)*IF(A2:M2="c",0,1)),"no","yes")
as an array formula (confirm with ctrl+shift+enter) in N2?

[Ron Coderre]

Using your sample workbook, this regular formula, copied down, returns: Yes if all referenced cells equal C, otherwise: No

Please Login or Register  to view this content.

Is that something you can work with?

[sunshine82]

@yudlugar, thank you for your help but it didn't work :-(

@Ron, that worked perfectly! Many thanks for your help.

Could you please advise what the ,2 and =7 is for so I can understand the formula better for future?

[ragulduy]

sunshine, thanks for the feedback

did you confirm it has an array formula (control+shift+enter instead of just enter when putting the formula in?)

[Ron Coderre]

Sure...
In this formula: =IF(SUMPRODUCT(MOD(COLUMN(A2:M2),2)*(A2:M2="C"))=7,"Yes","No")
this section: MOD(COLUMN(A2:M2),2)
returns the MOD ("leftover amount") when the column number is divided by 2.
(Col_A is column number 1, Col_B is column number 2, Col_C is column number 3, etc)
Odd column numbers have a mod of 1.
Even column numbers have a mod of 0.

So, that part returns an array of these values {1,0,1,0,1,0,1,0,1,0,1,0,1}
which are multiplied by the test for cell values that equal "C".

The 7 is the count of relevant cells in the referenced range (A, C, E, G, I, K, M).
So if the count of C's is 7, then ALL of the relevant cells contain "C" and the result is Yes.

Does that help?

[sunshine82]

@yudlugar, sorry that was silly of me, I didn't do the control+shift+enter at the end. That has worked perfectly as well, thanks very much.

@Ron, that makes a lot more sense, thank you for the clarification.

The problem is when using both formulas provided on the file I'm working on, it doesn't work even though I've changed the cells etc. I have now copied and pasted the data into exactly the same cells I am using on my work file on sheet 2. Could you please tell me the formula based on these cells?

Also @yudlugar, if you don't mind, could you explain what the formula is doing as Ron has described above.

[ragulduy]

Hi:
=IF(SUM(IF(ODD(COLUMN(A2:M2))=COLUMN(A2:M2),1,0)*IF(A2:M2="c",0,1)),"no","yes")


IF(ODD(COLUMN(A2:M2))=COLUMN(A2:M2),1,0)
is what I came up with to test if the column was an "odd" number (because every other cell had "c" in it).
The odd function rounds the number to the nearest odd number, so if it is an even number, it will return 0, and odd number will return 1.

Note - I didn't use ISODD() because it didn't seem to work in array formula.

IF(A2:M2="c",0,1)
then returns 0 if the cell has c in it or 1 if it does not.

I'm then left with 2 arrays of numbers (one element in the array for each cell), one that is 0 for even and one that is 0 for "c" cells.

I then multiply these arrays together, which means, that if there are any odd columns that do not contain c, they will remain as a "1". Taking the sum of this array will return 0 if all the odd columns have c in them, which is what I then test to assign no/yes.

[Ron Coderre]

Your new range begins in an EVEN number column...so your formula needs to reverse the MOD logic.
Try this regular formula:

Please Login or Register  to view this content.

Does that help?

[sunshine82]

Hi Both,

Thank you for all your help, the forumlas worked perfectly. Very much appreciated.

Kind Regards,
S