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

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

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

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

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