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

[Yanou91]

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 formula who gives 0 isntead of the right result :

=SUMPRODUCT(('Extraction SAP'!$Z$3:$Z$1000000)*('Extraction SAP'!$AA$3:$AA$1000000=B4)*('Extraction SAP'!$C$3:$C$1000000="9TRACTIV")*('Extraction SAP'!$AD$3:$AD$1000000="OUI")*(('Extraction SAP'!$M$3:$M$1000000="=G/*")+('Extraction SAP'!$M$3:$M$1000000="<>*GENERIQU*"))*(('Extraction SAP'!$E$3:$E$1000000="CO")+('Extraction SAP'!$E$3:$E$1000000="WE")+('Extraction SAP'!$E$3:$E$1000000="WA")))

Description :

'Extraction SAP'!$AA$3:$AA$1000000=B4 >>> column of the smal analytic code
'Extraction SAP'!$Z$3:$Z$1000000 >>> column of the accounting amounts to add
'Extraction SAP'!$C$3:$C$1000000="9TRACTIV" >>> column of the general accounts
'Extraction SAP'!$AD$3:$AD$1000000="YES" >>> column of the eligibility condition
'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)
'Extraction SAP'!$E$3:$E$1000000="CO" >>> column of the ktype of account (to do the sum if code is CO or WA or WE

Please someone tell me where is the mistake and how to get this formula right ? and not 0 as a result

Sorry I can't give the data base.

Thanks for help

Yannick

[#2 · Accepted solution]

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

[#3]

@mtarler 

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

[#4]

Hi,

I have just tried your formula. It shows a result and not 0 (good :grinning_face_with_sweat:). But strangly, the result is doubled versus the right result. I'm searching why.

[#5]

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

[#6]

I owe you an apology. I did a little mistake. You formula is perfect. Thanks you