Adding formulas

[bolludvi]

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;B8;0)+HVIS(F9=C83;B9;0)+HVIS(F10=C83;B10;0)+HVIS(F11=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;B18;0)+HVIS(F19=C83;B19;0)+HVIS(F20=C83;B20;0)+HVIS(F21=C83;B21;0)+HVIS(F22=C83;B22;0)+HVIS(F23=C83;B23;0)+HVIS(F24=C83;B24;0)+HVIS(F25=C83;B25;0)+HVIS(F26=C83;B26;0)+HVIS(F27=C83;B27;0)

Thanks

 

[Dave Peterson]

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.

 

[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)"

 

[Elkar]

Try this:

(In English)
=SUMPRODUCT(--(F6:F27=C83),B6:B27)

Or SUMPRODUKT in Danish I think?

HTH
Elkar

 

[Pete_UK]

This should do it:

=SUM.HVIS(F6:F27;C83;B6:B27)

Hope this helps.

Pete