Forum Discussion

Yanou91's avatar
Yanou91
Copper Contributor
Oct 13, 2022
Solved

To use SUMPRODUCT with several "OR" and several "AND"

Good evening,   I have an accounting data base with a certain number of column and I would like to use SUMPRODUCT to realise the add of values according criteras on others columns.   Here is the ...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Oct 13, 2022

    Yanou91 These 2 items are wrong:

    'Extraction SAP'!$M$3:$M$1000000="=G/*" >>> column of the tall analytic code (to do the sum if code starts with G/)
    'Extraction SAP'!$M$3:$M$1000000="<>*GENERIQU*" >>> column of the tall analytic code (to do the sum if code does not contain GENERIQU)

    try these instead:

    LEFT('Extraction SAP'!$M$3:$M$1000000,2)="G/" 
    NOT(ISNUMBER(SEARCH("GENERIQU",'Extraction SAP'!$M$3:$M$1000000)))

Yanou91's avatar
Yanou91
Copper Contributor
Oct 13, 2022

mtarler 

 

Thanks for your Reply. I gonna try it soon, tonight.

Yanou91's avatar
Yanou91
Copper Contributor
Oct 13, 2022
Hi,

I have just tried your formula. It shows a result and not 0 (good 😅). But strangly, the result is doubled versus the right result. I'm searching why.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Oct 13, 2022
    you have a couple of the terms added ( (condition1) + (condition2) ) and if BOTH are true it results in *2 instead of *2. You can use OR( condition1, condition2) or you could MAX(1, (condition1) + (condition2) ).
    • Yanou91's avatar
      Yanou91
      Copper Contributor
      Oct 13, 2022
      I owe you an apology. I did a little mistake. You formula is perfect. Thanks you