Conditional Counting (sumproduct)

[m0TiVe]

I am trying to 'port' an Excel-file to the OpenOffice suite. One thing i am struggling with is the conditional counting i 'fixed' in Excel. For example:

A B C
1 yes 100 200
2 no 50 100
3 yes 40 0
4 yes 0 0

In Excel i used: SUMPRODUCT((A1:A3="yes")*(B1:B3>0)
to get a total sum of column B where the same row has 'yes' in column A (in this case 140). Needless to say this worked fine. To sum columns B and C more work was needed in Excel (macro).

In OpenOffice.org Calc it returns #VALUE!

My questions:
* How do i get this to be calculated properly again?
* Can it be done with multiple columns (e.g. B1:C3 wich would result in 340 in this case)?

Thanks!

 

[TonyGroves]

In OOo, SUMPRODUCT takes a number of static arrays as arguments and returns the sum of the products of the corresponding elements. I don't know if that's the same as the equivalent Excel function.

To make it work here, you would need to convert column A to TRUE/FALSE or 1/0 values (or insert a new column with calculated values). You could then use the formula =SUMPRODUCT(A1:A3;B1:B3) .

To process the 2D-array B1:C3 as described, you could use the formula =SUMPRODUCT(A1:A3;B1:B3)+SUMPRODUCT(A1:A3;C1:C3) .