Question posted in
Some documentation to get you started can be found here.

sumproduct function in apache poi (version 3.15) with if condition is not working

I’m using below formula to calculate sum product with if condition using Apache poi 3.15 version, but it’s not evaluating with if condition (–(A1:A6=”A”)) and its giving “#VALUE” error.
The same formula if i use directly in excel, its working as expected. and if I remove “–(A1:A6=”A”)” this condition, it works fine with poi.

Formula:

=SUMPRODUCT(--(A1:A6="A"),B1:B6,C1:C6)

Could you please tell me how to evaluate this kind of formula or is there any alternative way to evaluate this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The issue is not with the formula, it's with the workbook which I'm using. The workbook SXSSFWorkbook has an issue with evaluation. When I changed to XSSFWorkbook, it started evaluating properly.

    I suggest using either XSSFWorkbook(.xlsx) or HSSFWorkbook(.xls) in order to make the formula work properly. 

    =SUMPRODUCT(B1:B6,C1:C6,--(A1:A6="A"))

    Note: Do not put IF condition (--(A1:A6="A")) as first argument.

    Thanks


  2. 0

    Okay, sorry for originally barking up the wrong tree…

    A fresh pair of Monday eyes and I can see the issue:

    You are using quotes " inside a quoted string, you need to escape the quotes in the formula with – cell.setCellFormula("SUMPRODUCT(--(A1:A6="A"),B1:B6,C1:C6)");

    =SUMPRODUCT(--(A1:A6="A")*(B1:B6)*(C1:C6)) – Edited to desired logic

    By using the double unary “--” you convert true/false in to 0 or 1.

    This is an if condition, so you need to also multiple each row of data by the 0 or 1 and sum the result; when the condition is false the product of the row will multiply by 0 giving a total of 0 to be summed for that row.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search