sumif more conditions

[Pierre via OfficeKB.com]

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

 

[Bob Phillips]

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

 

[Peo Sjoblom]

Try

=SOMPRODUCT((LEFT('nieuwe productie 2006'!G4:G1004,3)="DUO")*
('nieuwe productie 2006'!O4:O1004="afgesloten")*('nieuwe productie
2006'!S4:S1004))

 

[Pierre via OfficeKB.com]

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

=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

 

[Pierre via OfficeKB.com]

Thanks Peo.
This works fine...
Pierre

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

 

[Peo Sjoblom]

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

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

=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

 

[Bob Phillips]

Oops, sorry about that. Made sure I got LINKS, and forgot the delimiter!

--

HTH

RP
(remove nothere from the email address if mailing direct)

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

=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