MULTIPLE IF AND OR FUNCTIONS

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
443
Office Version
  1. 2019
Platform
  1. Windows
Hello experts, I am posting the image of the file with full details. If it is possible to enter a formula as discussed in the sheet.....
Query Multiple IF's.xlsx
ABCDEFGHJLNPQRSTUVWX
1STATIC VALUEVARIABLE COLUMNSCOLUMNS WHERE THE FORMULA TO BE ENTEREDVARIABLE COLUMNS
2GRAND TOTALA TOTALB TOTALABCDABCDR/ODiff
3Rows for reference onlyxx51218282.56914
41051001002.5-50ROW 4ROW 5ROW 6ROW 7
51121001006-120IF B4="",""IF B5="",""IF B6="",""IF B7="",""
61181001009-180IF J4,L4 AND N4=""IF H5, L5 AND N5=""IF H6,J6 AND N6=""IFJ7, J7 AND L7=""
712810010014-280THEN E4,F4 AND G4=""THEN D5,F5 AND G5="" THEN D6, E6 AND G6=""THEN D7,E7 AND F7=""
82172001001002.56-170AND D4=B4AND E5=N5/6*100AND F6=P6/9*100AND G7=R7/14*100
923020010010069-300
10246200100100914-460ROW 8ROW 9ROW 10ROW 11
112232001001002.59-230IF B8="",""IF B9="",""IF B10="",""IF B11="",""
122332001001002.514-330IF L8 AND N8=""IF H9 AND N9=""IF H10 AND J10=""IF J11 AND N11=""
133353001001001002.569-350THEN F8 AND G8 =""THEN D9 AND G9=""THEN D10 AND E10=""THEN E11 AND G11 =""
143583001001001006914-580AND D8=H8/2.5*100AND E9=J9/6*100AND F10=L10/9*100AND D11=H11/2.5*100
153513001001001002.5914-510ANDAND ANDAND
163453001001001002.5614-450F8=B8-D8G8=B9-E9G10=B10-F10F11=B11-D11
174634001001001001002.56914-630
18240200100100614-400ROW 12ROW 13ROW 14ROW 15
19IF B12="",""IF B13="",""IF B14="",""IF B15="",""
20I WILL TRY TO EXPLAIN MY PROBLEM AS SIMPLE AS POSSIBLEIF J12 AND L12=""IF N13=""IF H14=""IF J15=""
21If you change the amount in column B the difference amount in column Y should be zero onlyTHEN E12 AND F12 =""THAN G13=""THAN D14=""THAN E15=""
22I have given different formulas manually in columns D to KAND D12=H12/2.5*100THEN D13=H13/2.5*100AND E14=J14/6*100AND D15=H15/2.5*100
23There can be 15 different calculations for each amount entered in Column B ANDANDANDAND
24The number of entries are in 1000's. I have been calculating it manually for each row using sort and filter optionsG12=B12=D12E13=J13/6*100F14=L14/9*100F15=L15/9*100
25I know it is possible to make it easy by giving a formula in the Row 4 from D4:K4 in each column and copy the formula till the last entryANDANDAND
26Due to lack of knowledge of multiple "IF", "AND" & "OR", I am finding it difficult to solve this. I would like to know and understandF13=B13-H13-J13G14=B14-J14-L14G15=B15-D15-L15
27Since the last few weeks I have been trying but not able to solve it. I would really appreciate any help in solving this.
28Why don't you experts give it a try. If the solution is solved it will save me hours of workROW 16ROW 17ROW 18
29Please note that the amounts in columns H:P are also variables like in column BIF B16="",""IF B17="",""IF B18="",""
30Column C is an extension of my project and it will require to change only the references once I find a solutionIF J16=""IF H17, J17,L17 AND N17<>""IF H18 AND L18 =""
31How it worksTHEN F16=""THEN D17=H16/2.5*100THEN D18="", F18=""
32In columns H,J,L and N if there is amount in one cell only then it takes the amount from column BAND D16=H16/2.5*100AND E17=J17/6*100AND E18=J18/6*100
33if there are amounts in any 2 cells in H, J, L and N then the first cell in D, E, F and G should be calculated from the corresponding column ANDAND F17=L17/9*100AND G18=B18-E18
34and the second cell amount should be taken after deducting the total amount from the first calculated amountF16= L16/9*100AND G17=B17-D17-E17-F17
35if there are amounts in 3 cells the again the first cell amount should be calculated from the corresponding column AND
36the second amount also to be calculated from the corresponding columnG16=B16-D16-F16
37and then in the third cell it should deduct the total amount from the first and second calculation amounts
38if there are amounts in all 4 cells then the first cell amount should be calculated from the corresponding column
39the second cell amount also to be calculated from the corresponding column
40the third cell amount also to be calculated from the corresponding column
41and the fourth cell it should deduct the total amount from the first, second and third calculated amounts
42
43
44
45
QUERY
Cell Formulas
RangeFormula
D4D4=B4
P4:P18P4=B4+C4-SUM(D4:O4)
Q4:Q18Q4=A4-SUM(D4:O4)
E5E5=B5
F6F6=B6
G7G7=B7
D8,D15:D17,D11:D13D8=H8/2.5*100
E8E8=B8-D8
E9,E16:E17,E13:E14E9=J9/6*100
G10G10=B10-F10
F9F9=B9-E9
F10,F17,F14:F15F10=L10/9*100
F11F11=B11-D11
G12G12=B12-D12
F13F13=B13-D13-E13
G14G14=B14-E14-F14
G15G15=B15-D15-F15
G16G16=B16-D16-E16
A4:A17A4=SUM(C4:O4)
G17G17=B17-D17-E17-F17
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,553
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Use this 8 formula for 8 column. it is easier to understand & if need correcting, modifying it.
For H2:
Excel Formula:
=IF($P2="","",IF(AND(R2="",T2="",V2=""),$F2,$P2/2.5*100))
I2:
Excel Formula:
=IF($R2="","",IF(AND($P2="",$T2="",$V2=""),$F2,IF($P2="",$R2/6*100,IF(AND($T2="",$V2=""),$F2-IF($H2="",0,$H2),$R2/6*100))))
J2:
Excel Formula:
=IF($T2="","",IF(AND($P2="",$R2="",$V2=""),$F2,IF(AND($P2="",$R2=""),$T2/9*100,IF($V2="",$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)),$T2/9*100))))
K2:
Excel Formula:
=IF($V2="","",IF(AND($P2="",$R2="",$T2=""),$F2,$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)+IF($J2="",0,$J2))))

L2:
Excel Formula:
=IF($X2="","",IF(AND($Y2="",$Z2="",$AA2=""),$G2,$X2/5*100))
M2:
Excel Formula:
=IF($Y2="","",IF(AND($X2="",$Z2="",$AA2=""),$G2,IF($X2="",$Y2/12*100,IF(AND($Z2="",$AA2=""),$G2-IF($L2="",0,$L2),$Y2/12*100))))
N2:
Excel Formula:
=IF($Z2="","",IF(AND($X2="",$Y2="",$AA2=""),$G2,IF(AND($X2="",$Y2=""),$Z2/18*100,IF($AA2="",$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)),$Z2/18*100))))
O2:
Excel Formula:
=IF($AA2="","",IF(AND($X2="",$Y2="",$Z2=""),$G2,$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)+IF($N2="",0,$N2))))
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
443
Office Version
  1. 2019
Platform
  1. Windows
I think with this situation it is better tp use formula for each column seperately, to haven't circular references. Then i work on 8 formula for 8 column and post here.
Manually it is possible to do the calculation by 8 formulas in the 8 columns to get the desired correct result. But as the data is very huge and the problem is that it has 28 different conditions. So, each cell has to have 28 conditions in its formula.
I had been trying for 1 correct formula in 1 cell and drag it to the right and copy down but couldn't do it.
After you gave me the 2 formulas, I knew it would work. But there are only a few errors to be rectified which only you can do it.
It will work best if 2 formulas are applied as the formula of 4 columns (H I J K & L M N O) are interconnected with column F and G respectively and in no way connected to any other column.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,553
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Then Use This at first Cell:
Excel Formula:
=IF(COLUMN()-COLUMN($F$2)=2,IF($P2="","",IF(AND(R2="",T2="",V2=""),$F2,$P2/2.5*100)),IF(COLUMN()-COLUMN($F$2)=3,IF($R2="","",IF(AND($P2="",$T2="",$V2=""),$F2,IF($P2="",$R2/6*100,IF(AND($T2="",$V2=""),$F2-IF($H2="",0,$H2),$R2/6*100)))),IF(COLUMN()-COLUMN($F$2)=4,IF($T2="","",IF(AND($P2="",$R2="",$V2=""),$F2,IF(AND($P2="",$R2=""),$T2/9*100,IF($V2="",$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)),$T2/9*100)))),IF(COLUMN()-COLUMN($F$2)=5,IF($V2="","",IF(AND($P2="",$R2="",$T2=""),$F2,$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)+IF($J2="",0,$J2)))),IF(COLUMN()-COLUMN($F$2)=6,IF($X2="","",IF(AND($Y2="",$Z2="",$AA2=""),$G2,$X2/5*100)),IF(COLUMN()-COLUMN($F$2)=7,IF($Y2="","",IF(AND($X2="",$Z2="",$AA2=""),$G2,IF($X2="",$Y2/12*100,IF(AND($Z2="",$AA2=""),$G2-IF($L2="",0,$L2),$Y2/12*100)))),IF(COLUMN()-COLUMN($F$2)=8,IF($Z2="","",IF(AND($X2="",$Y2="",$AA2=""),$G2,IF(AND($X2="",$Y2=""),$Z2/18*100,IF($AA2="",$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)),$Z2/18*100)))),IF(COLUMN()-COLUMN($F$2)=9,IF($AA2="","",IF(AND($X2="",$Y2="",$Z2=""),$G2,$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)+IF($N2="",0,$N2)))),""))))))))
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,553
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I forgot to Fix
Excel Formula:
AND(R2="",T2="",V2="")
change it:
Excel Formula:
AND($R2="",$T2="",$V2="")
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
443
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I forgot to Fix
Excel Formula:
AND(R2="",T2="",V2="")
change it:
Excel Formula:
AND($R2="",$T2="",$V2="")
Maabadi, Please understand. The cell is AB is no where connected to the formula. If I delete the AB column it should not effect the formula. The cell F2 is freezed which is also effecting the formula. Go to any of the cells say H10 and press F4, the formula is connected to F2 as it is freezed. One row is not connected to the other rows.
In short, F is connected to cells H I J K and P R T V only. Likewise G is connected to cells L M N O and Z Y Z AA only. Any reference given to any other cells is not correct. So, Please try 2 different formulas. So, When I sort the data, it should not effect the solution in the cells which has your formula.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,553
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
It is problem with Don't fixing Cell Address at first column. I correct it.
For F2 you could Change $F$2 to $F2, Never mind, I Change it.
Excel Formula:
=IF(COLUMN()-COLUMN($F2)=2,IF($P2="","",IF(AND($R2="",$T2="",$V2=""),$F2,$P2/2.5*100)),IF(COLUMN()-COLUMN($F2)=3,IF($R2="","",IF(AND($P2="",$T2="",$V2=""),$F2,IF($P2="",$R2/6*100,IF(AND($T2="",$V2=""),$F2-IF($H2="",0,$H2),$R2/6*100)))),IF(COLUMN()-COLUMN($F2)=4,IF($T2="","",IF(AND($P2="",$R2="",$V2=""),$F2,IF(AND($P2="",$R2=""),$T2/9*100,IF($V2="",$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)),$T2/9*100)))),IF(COLUMN()-COLUMN($F2)=5,IF($V2="","",IF(AND($P2="",$R2="",$T2=""),$F2,$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)+IF($J2="",0,$J2)))),IF(COLUMN()-COLUMN($G2)=5,IF($X2="","",IF(AND($Y2="",$Z2="",$AA2=""),$G2,$X2/5*100)),IF(COLUMN()-COLUMN($G2)=6,IF($Y2="","",IF(AND($X2="",$Z2="",$AA2=""),$G2,IF($X2="",$Y2/12*100,IF(AND($Z2="",$AA2=""),$G2-IF($L2="",0,$L2),$Y2/12*100)))),IF(COLUMN()-COLUMN($G2)=7,IF($Z2="","",IF(AND($X2="",$Y2="",$AA2=""),$G2,IF(AND($X2="",$Y2=""),$Z2/18*100,IF($AA2="",$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)),$Z2/18*100)))),IF(COLUMN()-COLUMN($G2)=8,IF($AA2="","",IF(AND($X2="",$Y2="",$Z2=""),$G2,$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)+IF($N2="",0,$N2)))),""))))))))

If you want 2 different formula Use These:
Excel Formula:
=IF(COLUMN()-COLUMN($F2)=2,IF($P2="","",IF(AND($R2="",$T2="",$V2=""),$F2,$P2/2.5*100)),IF(COLUMN()-COLUMN($F2)=3,IF($R2="","",IF(AND($P2="",$T2="",$V2=""),$F2,IF($P2="",$R2/6*100,IF(AND($T2="",$V2=""),$F2-IF($H2="",0,$H2),$R2/6*100)))),IF(COLUMN()-COLUMN($F2)=4,IF($T2="","",IF(AND($P2="",$R2="",$V2=""),$F2,IF(AND($P2="",$R2=""),$T2/9*100,IF($V2="",$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)),$T2/9*100)))),IF(COLUMN()-COLUMN($F2)=5,IF($V2="","",IF(AND($P2="",$R2="",$T2=""),$F2,$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)+IF($J2="",0,$J2)))),""))))

AND
Excel Formula:
=IF(COLUMN()-COLUMN($G2)=5,IF($X2="","",IF(AND($Y2="",$Z2="",$AA2=""),$G2,$X2/5*100)),IF(COLUMN()-COLUMN($G2)=6,IF($Y2="","",IF(AND($X2="",$Z2="",$AA2=""),$G2,IF($X2="",$Y2/12*100,IF(AND($Z2="",$AA2=""),$G2-IF($L2="",0,$L2),$Y2/12*100)))),IF(COLUMN()-COLUMN($G2)=7,IF($Z2="","",IF(AND($X2="",$Y2="",$AA2=""),$G2,IF(AND($X2="",$Y2=""),$Z2/18*100,IF($AA2="",$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)),$Z2/18*100)))),IF(COLUMN()-COLUMN($G2)=8,IF($AA2="","",IF(AND($X2="",$Y2="",$Z2=""),$G2,$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)+IF($N2="",0,$N2)))),""))))
 
Last edited:
Solution

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
443
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

It is problem with Don't fixing Cell Address at first column. I correct it.
For F2 you could Change $F$2 to $F2, Never mind, I Change it.
Excel Formula:
=IF(COLUMN()-COLUMN($F2)=2,IF($P2="","",IF(AND($R2="",$T2="",$V2=""),$F2,$P2/2.5*100)),IF(COLUMN()-COLUMN($F2)=3,IF($R2="","",IF(AND($P2="",$T2="",$V2=""),$F2,IF($P2="",$R2/6*100,IF(AND($T2="",$V2=""),$F2-IF($H2="",0,$H2),$R2/6*100)))),IF(COLUMN()-COLUMN($F2)=4,IF($T2="","",IF(AND($P2="",$R2="",$V2=""),$F2,IF(AND($P2="",$R2=""),$T2/9*100,IF($V2="",$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)),$T2/9*100)))),IF(COLUMN()-COLUMN($F2)=5,IF($V2="","",IF(AND($P2="",$R2="",$T2=""),$F2,$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)+IF($J2="",0,$J2)))),IF(COLUMN()-COLUMN($G2)=5,IF($X2="","",IF(AND($Y2="",$Z2="",$AA2=""),$G2,$X2/5*100)),IF(COLUMN()-COLUMN($G2)=6,IF($Y2="","",IF(AND($X2="",$Z2="",$AA2=""),$G2,IF($X2="",$Y2/12*100,IF(AND($Z2="",$AA2=""),$G2-IF($L2="",0,$L2),$Y2/12*100)))),IF(COLUMN()-COLUMN($G2)=7,IF($Z2="","",IF(AND($X2="",$Y2="",$AA2=""),$G2,IF(AND($X2="",$Y2=""),$Z2/18*100,IF($AA2="",$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)),$Z2/18*100)))),IF(COLUMN()-COLUMN($G2)=8,IF($AA2="","",IF(AND($X2="",$Y2="",$Z2=""),$G2,$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)+IF($N2="",0,$N2)))),""))))))))

If you want 2 different formula Use These:
Excel Formula:
=IF(COLUMN()-COLUMN($F2)=2,IF($P2="","",IF(AND($R2="",$T2="",$V2=""),$F2,$P2/2.5*100)),IF(COLUMN()-COLUMN($F2)=3,IF($R2="","",IF(AND($P2="",$T2="",$V2=""),$F2,IF($P2="",$R2/6*100,IF(AND($T2="",$V2=""),$F2-IF($H2="",0,$H2),$R2/6*100)))),IF(COLUMN()-COLUMN($F2)=4,IF($T2="","",IF(AND($P2="",$R2="",$V2=""),$F2,IF(AND($P2="",$R2=""),$T2/9*100,IF($V2="",$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)),$T2/9*100)))),IF(COLUMN()-COLUMN($F2)=5,IF($V2="","",IF(AND($P2="",$R2="",$T2=""),$F2,$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)+IF($J2="",0,$J2)))),""))))

AND
Excel Formula:
=IF(COLUMN()-COLUMN($G2)=5,IF($X2="","",IF(AND($Y2="",$Z2="",$AA2=""),$G2,$X2/5*100)),IF(COLUMN()-COLUMN($G2)=6,IF($Y2="","",IF(AND($X2="",$Z2="",$AA2=""),$G2,IF($X2="",$Y2/12*100,IF(AND($Z2="",$AA2=""),$G2-IF($L2="",0,$L2),$Y2/12*100)))),IF(COLUMN()-COLUMN($G2)=7,IF($Z2="","",IF(AND($X2="",$Y2="",$AA2=""),$G2,IF(AND($X2="",$Y2=""),$Z2/18*100,IF($AA2="",$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)),$Z2/18*100)))),IF(COLUMN()-COLUMN($G2)=8,IF($AA2="","",IF(AND($X2="",$Y2="",$Z2=""),$G2,$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)+IF($N2="",0,$N2)))),""))))
In type B the references are right but in type A you haven't taken the reference for column K. I will check once you have given the right references once for all. Did you sort the column and check. P;ease Sort the data by any of the columns F or G and check if the amounts of changed. If it doesn't change then maybe you got it right. I used the #47 formula.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,553
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I check based sourced of K Column and it is Column V, When V is empty, then K is empty. No Problem.
I check with all of your data and it 's working without problem for me.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
443
Office Version
  1. 2019
Platform
  1. Windows
I check based sourced of K Column and it is Column V, When V is empty, then K is empty. No Problem.
I check with all of your data and it 's working without problem for me.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
443
Office Version
  1. 2019
Platform
  1. Windows
Looks ok. GTG. Will try on the original data tonight and will let you know tonight.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,908
Messages
5,621,588
Members
415,844
Latest member
nigeywigey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top