Forum Discussion

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
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)))

mtarler

Silver Contributor

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

Copper Contributor

Oct 13, 2022

mtarler

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

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
  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
    Copper Contributor
    Oct 13, 2022
    I owe you an apology. I did a little mistake. You formula is perfect. Thanks you