|
sumif more conditions
Hi experts,
I have the following code that works fine: =SOMPRODUCT(('nieuwe productie 2006'!G4:G1004="DUO Bedrijfspensioen")* ('nieuwe productie 2006'!O4:O1004="afgesloten")*('nieuwe productie 2006'!S4: S1004)) The problem is that not only do i want to sum the cells where G4:G1004 = "DUO Bedrijfspensioen" but where the left 3 characters are "DUO" Any ideas how to adapt my code so that it only looks at the first three characters in G4:G1004? Thanks, Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200601/1 |
sumif more conditions
=SOMPRODUCT((LINKS('nieuwe productie 2006'!G4:G1004,3)="DUO")*
('nieuwe productie 2006'!O4:O1004="afgesloten")*('nieuwe productie 2006'!S4: S1004)) -- HTH RP (remove nothere from the email address if mailing direct) "Pierre via OfficeKB.com" <u13950@uwe wrote in message news:59c567156c97a@uwe... Hi experts, I have the following code that works fine: =SOMPRODUCT(('nieuwe productie 2006'!G4:G1004="DUO Bedrijfspensioen")* ('nieuwe productie 2006'!O4:O1004="afgesloten")*('nieuwe productie 2006'!S4: S1004)) The problem is that not only do i want to sum the cells where G4:G1004 = "DUO Bedrijfspensioen" but where the left 3 characters are "DUO" Any ideas how to adapt my code so that it only looks at the first three characters in G4:G1004? Thanks, Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200601/1 |
sumif more conditions
Try
=SOMPRODUCT((LEFT('nieuwe productie 2006'!G4:G1004,3)="DUO")* ('nieuwe productie 2006'!O4:O1004="afgesloten")*('nieuwe productie 2006'!S4:S1004)) -- Regards, Peo Sjoblom "Pierre via OfficeKB.com" <u13950@uwe wrote in message news:59c567156c97a@uwe... Hi experts, I have the following code that works fine: =SOMPRODUCT(('nieuwe productie 2006'!G4:G1004,3)="DUO Bedrijfspensioen")* ('nieuwe productie 2006'!O4:O1004="afgesloten")*('nieuwe productie 2006'!S4: S1004)) The problem is that not only do i want to sum the cells where G4:G1004 = "DUO Bedrijfspensioen" but where the left 3 characters are "DUO" Any ideas how to adapt my code so that it only looks at the first three characters in G4:G1004? Thanks, Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200601/1 |
sumif more conditions
thanks Bob,
The only thing that i had wrong is the , before the 3 ! In dutch this must be a ; .... Thanks Bob. Just another question: If i only want the number of entries instead of the sum of the entries, how would i adapt the code? Thanks, Pierre Bob Phillips wrote: =SOMPRODUCT((LINKS('nieuwe productie 2006'!G4:G1004,3)="DUO")* ('nieuwe productie 2006'!O4:O1004="afgesloten")*('nieuwe productie 2006'!S4: S1004)) Hi experts, [quoted text clipped - 12 lines] Thanks, Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200601/1 |
sumif more conditions
Thanks Peo.
This works fine... Pierre Peo Sjoblom wrote: Try =SOMPRODUCT((LEFT('nieuwe productie 2006'!G4:G1004,3)="DUO")* ('nieuwe productie 2006'!O4:O1004="afgesloten")*('nieuwe productie 2006'!S4:S1004)) Hi experts, [quoted text clipped - 12 lines] Thanks, Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200601/1 |
sumif more conditions
Remove the last array if you want to count
=SOMPRODUCT((LINKS('nieuwe productie 2006'!G4:G1004;3)="DUO")*('nieuwe productie 2006'!O4:O1004="afgesloten")) Regards, Peo Sjoblom "Pierre via OfficeKB.com" <u13950@uwe wrote in message news:59c5ad26388b2@uwe... thanks Bob, The only thing that i had wrong is the , before the 3 ! In dutch this must be a ; .... Thanks Bob. Just another question: If i only want the number of entries instead of the sum of the entries, how would i adapt the code? Thanks, Pierre Bob Phillips wrote: =SOMPRODUCT((LINKS('nieuwe productie 2006'!G4:G1004,3)="DUO")* ('nieuwe productie 2006'!O4:O1004="afgesloten")*('nieuwe productie 2006'!S4: S1004)) Hi experts, [quoted text clipped - 12 lines] Thanks, Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200601/1 |
sumif more conditions
Oops, sorry about that. Made sure I got LINKS, and forgot the delimiter!
-- HTH RP (remove nothere from the email address if mailing direct) "Pierre via OfficeKB.com" <u13950@uwe wrote in message news:59c5ad26388b2@uwe... thanks Bob, The only thing that i had wrong is the , before the 3 ! In dutch this must be a ; .... Thanks Bob. Just another question: If i only want the number of entries instead of the sum of the entries, how would i adapt the code? Thanks, Pierre Bob Phillips wrote: =SOMPRODUCT((LINKS('nieuwe productie 2006'!G4:G1004,3)="DUO")* ('nieuwe productie 2006'!O4:O1004="afgesloten")*('nieuwe productie 2006'!S4: S1004)) Hi experts, [quoted text clipped - 12 lines] Thanks, Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200601/1 |
| All times are GMT +1. The time now is 06:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com