Adding formulas
Hello
I wonder how I can make this formula shorter/easyer? Can anyone help me? =HVIS(F6=C83;B6;0)+HVIS(F7=C83;B7;0)+HVIS(F8=C83;B 8;0)+HVIS(F9=C83;B9;0)+HVIS(F10=C83;B10;0)+HVIS(F1 1=C83;B11;0)+HVIS(F12=C83;B12;0)+HVIS(F13=C83;B13; 0)+HVIS(F14=C83;B14;0)+HVIS(F15=C83;B15;0)+HVIS(F1 6=C83;B16;0)+HVIS(F17=C83;B17;0)+HVIS(F18=C83;B18; 0)+HVIS(F19=C83;B19;0)+HVIS(F20=C83;B20;0)+HVIS(F2 1=C83;B21;0)+HVIS(F22=C83;B22;0)+HVIS(F23=C83;B23; 0)+HVIS(F24=C83;B24;0)+HVIS(F25=C83;B25;0)+HVIS(F2 6=C83;B26;0)+HVIS(F27=C83;B27;0) Thanks |
Adding formulas
It looks like =hvis() translates into =if() in the English version.
Try this: Open excel Open your workbook. Go to an empty cell Hit alt-F11 to get to the VBE (where macros live) type this and hit enter: ActiveCell.Formula = "=sumif(f6:f27,c83,b6:b27)" Excel and VBA will translate the formula into your language using your separators. Then back to excel to see how it was translated and check to see if it does what you want. bolludvi wrote: Hello I wonder how I can make this formula shorter/easyer? Can anyone help me? =HVIS(F6=C83;B6;0)+HVIS(F7=C83;B7;0)+HVIS(F8=C83;B 8;0)+HVIS(F9=C83;B9;0)+HVIS(F10=C83;B10;0)+HVIS(F1 1=C83;B11;0)+HVIS(F12=C83;B12;0)+HVIS(F13=C83;B13; 0)+HVIS(F14=C83;B14;0)+HVIS(F15=C83;B15;0)+HVIS(F1 6=C83;B16;0)+HVIS(F17=C83;B17;0)+HVIS(F18=C83;B18; 0)+HVIS(F19=C83;B19;0)+HVIS(F20=C83;B20;0)+HVIS(F2 1=C83;B21;0)+HVIS(F22=C83;B22;0)+HVIS(F23=C83;B23; 0)+HVIS(F24=C83;B24;0)+HVIS(F25=C83;B25;0)+HVIS(F2 6=C83;B26;0)+HVIS(F27=C83;B27;0) Thanks -- Dave Peterson |
Adding formulas
I left out a step:
Open excel Open your workbook. Go to an empty cell Hit alt-F11 to get to the VBE (where macros live) hit ctrl-g to see the immediate window <----------Added!!!! type this and hit enter: ActiveCell.Formula = "=sumif(f6:f27,c83,b6:b27)" Dave Peterson wrote: It looks like =hvis() translates into =if() in the English version. Try this: Open excel Open your workbook. Go to an empty cell Hit alt-F11 to get to the VBE (where macros live) type this and hit enter: ActiveCell.Formula = "=sumif(f6:f27,c83,b6:b27)" Excel and VBA will translate the formula into your language using your separators. Then back to excel to see how it was translated and check to see if it does what you want. bolludvi wrote: Hello I wonder how I can make this formula shorter/easyer? Can anyone help me? =HVIS(F6=C83;B6;0)+HVIS(F7=C83;B7;0)+HVIS(F8=C83;B 8;0)+HVIS(F9=C83;B9;0)+HVIS(F10=C83;B10;0)+HVIS(F1 1=C83;B11;0)+HVIS(F12=C83;B12;0)+HVIS(F13=C83;B13; 0)+HVIS(F14=C83;B14;0)+HVIS(F15=C83;B15;0)+HVIS(F1 6=C83;B16;0)+HVIS(F17=C83;B17;0)+HVIS(F18=C83;B18; 0)+HVIS(F19=C83;B19;0)+HVIS(F20=C83;B20;0)+HVIS(F2 1=C83;B21;0)+HVIS(F22=C83;B22;0)+HVIS(F23=C83;B23; 0)+HVIS(F24=C83;B24;0)+HVIS(F25=C83;B25;0)+HVIS(F2 6=C83;B26;0)+HVIS(F27=C83;B27;0) Thanks -- Dave Peterson -- Dave Peterson |
Adding formulas
Try this:
(In English) =SUMPRODUCT(--(F6:F27=C83),B6:B27) Or SUMPRODUKT in Danish I think? HTH Elkar bolludvi" wrote: Hello I wonder how I can make this formula shorter/easyer? Can anyone help me? =HVIS(F6=C83;B6;0)+HVIS(F7=C83;B7;0)+HVIS(F8=C83;B 8;0)+HVIS(F9=C83;B9;0)+HVIS(F10=C83;B10;0)+HVIS(F1 1=C83;B11;0)+HVIS(F12=C83;B12;0)+HVIS(F13=C83;B13; 0)+HVIS(F14=C83;B14;0)+HVIS(F15=C83;B15;0)+HVIS(F1 6=C83;B16;0)+HVIS(F17=C83;B17;0)+HVIS(F18=C83;B18; 0)+HVIS(F19=C83;B19;0)+HVIS(F20=C83;B20;0)+HVIS(F2 1=C83;B21;0)+HVIS(F22=C83;B22;0)+HVIS(F23=C83;B23; 0)+HVIS(F24=C83;B24;0)+HVIS(F25=C83;B25;0)+HVIS(F2 6=C83;B26;0)+HVIS(F27=C83;B27;0) Thanks |
Adding formulas
This should do it:
=SUM.HVIS(F6:F27;C83;B6:B27) Hope this helps. Pete On Jan 30, 7:10*pm, bolludvi wrote: Hello I wonder how I can make this formula shorter/easyer? Can anyone help me? =HVIS(F6=C83;B6;0)+HVIS(F7=C83;B7;0)+HVIS(F8=C83;B 8;0)+HVIS(F9=C83;B9;0)+HV*IS(F10=C83;B10;0)+HVIS(F 11=C83;B11;0)+HVIS(F12=C83;B12;0)+HVIS(F13=C83;B13 ;*0)+HVIS(F14=C83;B14;0)+HVIS(F15=C83;B15;0)+HVIS( F16=C83;B16;0)+HVIS(F17=C83*;B17;0)+HVIS(F18=C83;B 18;0)+HVIS(F19=C83;B19;0)+HVIS(F20=C83;B20;0)+HVIS (F2*1=C83;B21;0)+HVIS(F22=C83;B22;0)+HVIS(F23=C83; B23;0)+HVIS(F24=C83;B24;0)+HV*IS(F25=C83;B25;0)+HV IS(F26=C83;B26;0)+HVIS(F27=C83;B27;0) Thanks |
All times are GMT +1. The time now is 02:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com