| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF with two conditions? | Excel Worksheet Functions | |||
| SUMIF function with 2 conditions | Excel Worksheet Functions | |||
| SUMIF with two conditions | Excel Discussion (Misc queries) | |||
| sumif with two conditions | Excel Worksheet Functions | |||
| SUMIF based on 2 conditions | Excel Worksheet Functions | |||
Linear Mode
