Sum of IF in cell N84

**olwy** · 06-17-2013, 06:23 AM

Dear all, please, I would like to simplify this formula anyhow, but I have no idea how.
It says - =if($E8="BBS";$AL8*N8;0) and goes 60 times for each new line. I assume throuhg sumifs, it should be easier, but how? It could be done by extra sheets where "al*n" will be calculated for each month, but I do not would like to add any extra cells.
Please help to rearange N84 (should be copy-able to P84 as well as to N85).

**Ace_XL** · 06-17-2013, 06:33 AM

Use

=SUMPRODUCT(--($E$8:$E$70="BBS"),N$8:N$70*$AL$8:$AL$70)

Copy across

**olwy** · 06-17-2013, 06:57 AM

Please, could you paste it into the file enclosed? I am using different excel language and just a translation of sumproduct is not enough. From my previous experience, when I open it - all syntaxt will be re-create proper way. Thans for it. Lenght of a formula is as I would like to have it - great.

**Ace_XL** · 06-17-2013, 07:20 AM

See attached.

All changed cells highlighted in yellow

**olwy** · 06-17-2013, 07:38 AM

Oh, great, it helps a lot. There is ";" instead of "," - so in my language it looks like :"SOUČIN.SKALÁRNÍ(--($E$8:$E$70="BBS");N$8:N$70*$AL$8:$AL$70)". Please, could you tell me, what is the meaning of "--" (double minus) here. Why it is not working without it.
It is really awesome.

**Ace_XL** · 06-17-2013, 07:41 AM

Yeah some Excel language versions use ; instead of ,

This should resolve your sumproduct and -- query
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

**olwy** · 06-17-2013, 08:08 AM

Thanks. Click * Post closed. ... thanks.

**olwy** · 06-17-2013, 08:15 AM

Just a note for other readers: "-" is here to declare column instead of formula. Without "-" it automatically takes rows. (as in detail described in link above).