Better solution (instead of using SUM.IF function)

[Plastikman]

It must a smoother solution to calculate total cost of a component etc. within a year instead of using SUM.IF (function.

(got red cross picture for some reason when I added a photo of the table. Link to the source: http://img263.imageshack.us/i/calcn.jpg/

SUM.IF gives of course right answer but the formula is way too long, I think.


My function to calculate:
=SUM.IF($A$5:$A$6000;P4;$B$5:$B$6000)+SUM.IF $A$5:$A$6000;P4;$C$5:$C$6000)+...+SUM.IF($A$5:$A$6000;P4;$M$5:$M$6000)


Note, I have a swedish version of Excel 2007, there might be some poor translation here.


Is it possbile to use PRODUCTSUM?

Thanks!

 

[T. Valko]

It must a smoother solution to calculate total cost of a component etc. within a year instead of using SUM.IF (function.

(got red cross picture for some reason when I added a photo of the table. Link to the source: http://img263.imageshack.us/i/calcn.jpg/

SUM.IF gives of course right answer but the formula is way too long, I think.


My function to calculate:
=SUM.IF($A$5:$A$6000;P4;$B$5:$B$6000)+SUM.IF $A$5:$A$6000;P4;$C$5:$C$6000)+...+SUM.IF($A$5:$A$6000;P4;$M$5:$M$6000)


Note, I have a swedish version of Excel 2007, there might be some poor translation here.


Is it possbile to use PRODUCTSUM?

Thanks!

Try this...

=SUMPRODUCT((A5:A6000=P4)*B5:M6000)

 

[Aladin Akyurek]

It must a smoother solution to calculate total cost of a component etc. within a year instead of using SUM.IF (function.

(got red cross picture for some reason when I added a photo of the table. Link to the source: http://img263.imageshack.us/i/calcn.jpg/

SUM.IF gives of course right answer but the formula is way too long, I think.


My function to calculate:
=SUM.IF($A$5:$A$6000;P4;$B$5:$B$6000)+SUM.IF $A$5:$A$6000;P4;$C$5:$C$6000)+...+SUM.IF($A$5:$A$6000;P4;$M$5:$M$6000)


Note, I have a swedish version of Excel 2007, there might be some poor translation here.


Is it possbile to use PRODUCTSUM?

Thanks!

If the range to sum runs from column A to column M...

Control+shift+enter, not just enter:

=SUMMA(OM($A$5:$A$6000=P4;$B$5:$M$6000))

 

[Plastikman]

Many thanks for your quick answers, T.Valko & Aladin Akyurek!
By far much better ways to enter the calc

 

[Aladin Akyurek]

Many thanks for your quick answers, T.Valko & Aladin Akyurek!
By far much better ways to enter the calc

You are welcome. Thanks for providing feedback.

Just a side note though... Whenever one needs to mix vectors and matrices,
the best thing to do is to avoid PRODUKTSUMMA.

 

[Plastikman]

You are welcome. Thanks for providing feedback.

Just a side note though... Whenever one needs to mix vectors and matrices,
the best thing to do is to avoid PRODUKTSUMMA.

Thanks for the advice!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
How come you know the Swedish formulas? Did you Google or it possible to get this info via Excel using some VBA code etc?

 

[Aladin Akyurek]

Thanks for the advice!

You are welcome.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

How come you know the Swedish formulas? Did you Google or it possible to get this info via Excel using some VBA code etc?

I'm using a system Daniel Maher created, which translates a formula from one language into another.

 

[T. Valko]

Many thanks for your quick answers, T.Valko & Aladin Akyurek!
By far much better ways to enter the calc

You're welcome. Thanks for the feedback!