Related:

- Comparing columns and returning value
- Comparing columns and returning value ✓ - Forum - Excel
- Excel formula to compare two columns and return a value ✓ - Forum - Excel
- Compare Column A to Column B and return Column C ✓ - Forum - Excel
- Comparing columns in two different excel sheets and if they match copying third column ✓ - Forum - Excel
- Excel - Compare column A & B give results in column C - How-To - Excel

Thakkudu

- Posts
- 5
- Registration date
- Sunday February 17, 2013
- Status
- Member
- Last seen
- February 19, 2013

Hi

Actully, I need to compare 12 columns.(12 months of an year). With IF statement I can use only 7 'Ifs' and the ramining 5 columns remains without comparing.

Actully, I need to compare 12 columns.(12 months of an year). With IF statement I can use only 7 'Ifs' and the ramining 5 columns remains without comparing.

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

look into IF statement

TrowaD

- Posts
- 2674
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 27, 2020

Hi Arlene,

Not sure I understand your question?

You want to replace all the cells containing "False" with its corresponding column header?

Can you give an example and also mention to what range it applies?

Best regards,

Trowa

Not sure I understand your question?

You want to replace all the cells containing "False" with its corresponding column header?

Can you give an example and also mention to what range it applies?

Best regards,

Trowa

Kevin@Radstock

- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Status
- Member
- Last seen
- April 26, 2014

Hi Thakkudu

For your data in post #1, the following formula should cover that. In D1 & copy down.

=IF(AND(A1>0,COUNTA(B1:C1)=0),140*1,IF(AND(B1>0,C1=""),140*2,""))

Perhaps it would be better if you posted a sample worksheet with dummy data.

Kevin

@ rizvisa1

Are you ever going to post a example of what you suggest!!

For your data in post #1, the following formula should cover that. In D1 & copy down.

=IF(AND(A1>0,COUNTA(B1:C1)=0),140*1,IF(AND(B1>0,C1=""),140*2,""))

Perhaps it would be better if you posted a sample worksheet with dummy data.

Kevin

@ rizvisa1

Are you ever going to post a example of what you suggest!!

TrowaD

- Posts
- 2674
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 27, 2020

@ Kevin@Radstock

What is the point of giving an answer if you don't know the full extent of the query?

Your answer is rubbish considering Thakkudu's reply on Feb 17, 2013 11:09PM.

So it's not a bad idea to ask some questions to get a better idea/understanding of what needs to be solved.

What is the point of giving an answer if you don't know the full extent of the query?

Your answer is rubbish considering Thakkudu's reply on Feb 17, 2013 11:09PM.

So it's not a bad idea to ask some questions to get a better idea/understanding of what needs to be solved.

TrowaD

- Posts
- 2674
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 27, 2020

Hi Thakkudu,

You can use more than 7 if's if you are willing to sacrifise another column.

This formula should be placed where you want the result:

=IF(L2<>"",12*140,IF(K2<>"",11*140,IF(J2<>"",10*140,IF(I2<>"",9*140,IF(H2<>"",8*140,N2)))))

When all of these if statements are false, the formula will look at N2. You can change this to whatever column you want to use. Since you won't be doing anything else with this data the column can be hidden or the text made white.

The formula in N2 (or wherever) will look like:

=IF(G2<>"",7*140,IF(F2<>"",6*140,IF(E2<>"",5*140,IF(D2<>"",4*140,IF(C2<>"",3*140,IF(B2<>"",2*140,IF(A2<>"",1*140,"")))))))

So two formula's working in harmony to give you the desired result.

Best regards,

Trowa

You can use more than 7 if's if you are willing to sacrifise another column.

This formula should be placed where you want the result:

=IF(L2<>"",12*140,IF(K2<>"",11*140,IF(J2<>"",10*140,IF(I2<>"",9*140,IF(H2<>"",8*140,N2)))))

When all of these if statements are false, the formula will look at N2. You can change this to whatever column you want to use. Since you won't be doing anything else with this data the column can be hidden or the text made white.

The formula in N2 (or wherever) will look like:

=IF(G2<>"",7*140,IF(F2<>"",6*140,IF(E2<>"",5*140,IF(D2<>"",4*140,IF(C2<>"",3*140,IF(B2<>"",2*140,IF(A2<>"",1*140,"")))))))

So two formula's working in harmony to give you the desired result.

Best regards,

Trowa

Thakkudu

- Posts
- 5
- Registration date
- Sunday February 17, 2013
- Status
- Member
- Last seen
- February 19, 2013

Hi Trowa

This worked and is really helpful. I can use this for my other worksheets also.

Thank you sooooooooooooooo much.

regards

This worked and is really helpful. I can use this for my other worksheets also.

Thank you sooooooooooooooo much.

regards

Can you have more than one column Filled in. If yes then then what should happen ?

You are right. The multiple I use with 140 refers to month.

I have data in more than one column.

I want if column Jan has some value answer should be 140*1

If column Feb has some value answer should be 140*2 and so on until Dec.

does the header of the column shows date or month?

For eg: If feb has a data 140*2

If mar has a data 140*3 and so on

the column header shows month

but lets say your dates start from Column A till Column L

and you want the result in column N

and there are months like JANUARY etc in row 1

then you can try some thing like this

=140 * MONTH("01-" & INDEX(A1:L1,MATCH(9.99999999999999E+307,L2:L2)) & "-2013")