software

Hi experts,

I have the following code that works fine:

=SOMPRODUCT(('nieuwe productie 2006'!G4:G1004=quot;DUO Bedrijfspensioenquot;)*
('nieuwe productie 2006'!O4:O1004=quot;afgeslotenquot;)*('nieuwe productie 2006'!S4:
S1004))

The problem is that not only do i want to sum the cells where G4:G1004 = quot;DUO
Bedrijfspensioenquot; but where
the left 3 characters are quot;DUOquot;

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
www.officekb.com/Uwe/Forums.a...tions/200601/1

=SOMPRODUCT((LINKS('nieuwe productie 2006'!G4:G1004,3)=quot;DUOquot;)*
('nieuwe productie 2006'!O4:O1004=quot;afgeslotenquot;)*('nieuwe productie 2006'!S4:
S1004))--

HTH

RP
(remove nothere from the email address if mailing direct)quot;Pierre via OfficeKB.comquot; lt;u13950@uwegt; wrote in message
news:59c567156c97a@uwe...
gt; Hi experts,
gt;
gt; I have the following code that works fine:
gt;
gt; =SOMPRODUCT(('nieuwe productie 2006'!G4:G1004=quot;DUO Bedrijfspensioenquot;)*
gt; ('nieuwe productie 2006'!O4:O1004=quot;afgeslotenquot;)*('nieuwe productie
2006'!S4:
gt; S1004))
gt;
gt; The problem is that not only do i want to sum the cells where G4:G1004 =
quot;DUO
gt; Bedrijfspensioenquot; but where
gt; the left 3 characters are quot;DUOquot;
gt;
gt; Any ideas how to adapt my code so that it only looks at the first three
gt; characters in G4:G1004?
gt; Thanks,
gt; Pierre
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...tions/200601/1
Try

=SOMPRODUCT((LEFT('nieuwe productie 2006'!G4:G1004,3)=quot;DUOquot;)*
('nieuwe productie 2006'!O4:O1004=quot;afgeslotenquot;)*('nieuwe productie
2006'!S4:S1004))--

Regards,

Peo Sjoblom
quot;Pierre via OfficeKB.comquot; lt;u13950@uwegt; wrote in message
news:59c567156c97a@uwe...
gt; Hi experts,
gt;
gt; I have the following code that works fine:
gt;
gt; =SOMPRODUCT(('nieuwe productie 2006'!G4:G1004,3)=quot;DUO Bedrijfspensioenquot;)*
gt; ('nieuwe productie 2006'!O4:O1004=quot;afgeslotenquot;)*('nieuwe productie
2006'!S4:
gt; S1004))
gt;
gt; The problem is that not only do i want to sum the cells where G4:G1004 =
quot;DUO
gt; Bedrijfspensioenquot; but where
gt; the left 3 characters are quot;DUOquot;
gt;
gt; Any ideas how to adapt my code so that it only looks at the first three
gt; characters in G4:G1004?
gt; Thanks,
gt; Pierre
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...tions/200601/1
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,
PierreBob Phillips wrote:
gt;=SOMPRODUCT((LINKS('nieuwe productie 2006'!G4:G1004,3)=quot;DUOquot;)*
gt;('nieuwe productie 2006'!O4:O1004=quot;afgeslotenquot;)*('nieuwe productie 2006'!S4:
gt;S1004))
gt;
gt;gt; Hi experts,
gt;gt;
gt;[quoted text clipped - 12 lines]
gt;gt; Thanks,
gt;gt; Pierre

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...tions/200601/1

Thanks Peo.
This works fine...
Pierre

Peo Sjoblom wrote:
gt;Try
gt;
gt;=SOMPRODUCT((LEFT('nieuwe productie 2006'!G4:G1004,3)=quot;DUOquot;)*
gt;('nieuwe productie 2006'!O4:O1004=quot;afgeslotenquot;)*('nieuwe productie
gt;2006'!S4:S1004))
gt;
gt;gt; Hi experts,
gt;gt;
gt;[quoted text clipped - 12 lines]
gt;gt; Thanks,
gt;gt; Pierre

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...tions/200601/1

Remove the last array if you want to count

=SOMPRODUCT((LINKS('nieuwe productie 2006'!G4:G1004;3)=quot;DUOquot;)*('nieuwe
productie 2006'!O4:O1004=quot;afgeslotenquot;))

Regards,

Peo Sjoblom

quot;Pierre via OfficeKB.comquot; lt;u13950@uwegt; wrote in message
news:59c5ad26388b2@uwe...
gt; thanks Bob,
gt;
gt; The only thing that i had wrong is the , before the 3 ! In dutch this must
be
gt; a ; ....
gt; Thanks Bob.
gt;
gt; Just another question:
gt; If i only want the number of entries instead of the sum of the entries,
how
gt; would i adapt the code?
gt; Thanks,
gt; Pierre
gt;
gt;
gt; Bob Phillips wrote:
gt; gt;=SOMPRODUCT((LINKS('nieuwe productie 2006'!G4:G1004,3)=quot;DUOquot;)*
gt; gt;('nieuwe productie 2006'!O4:O1004=quot;afgeslotenquot;)*('nieuwe productie
2006'!S4:
gt; gt;S1004))
gt; gt;
gt; gt;gt; Hi experts,
gt; gt;gt;
gt; gt;[quoted text clipped - 12 lines]
gt; gt;gt; Thanks,
gt; gt;gt; Pierre
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...tions/200601/1
Oops, sorry about that. Made sure I got LINKS, and forgot the delimiter!

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;Pierre via OfficeKB.comquot; lt;u13950@uwegt; wrote in message
news:59c5ad26388b2@uwe...
gt; thanks Bob,
gt;
gt; The only thing that i had wrong is the , before the 3 ! In dutch this must
be
gt; a ; ....
gt; Thanks Bob.
gt;
gt; Just another question:
gt; If i only want the number of entries instead of the sum of the entries,
how
gt; would i adapt the code?
gt; Thanks,
gt; Pierre
gt;
gt;
gt; Bob Phillips wrote:
gt; gt;=SOMPRODUCT((LINKS('nieuwe productie 2006'!G4:G1004,3)=quot;DUOquot;)*
gt; gt;('nieuwe productie 2006'!O4:O1004=quot;afgeslotenquot;)*('nieuwe productie
2006'!S4:
gt; gt;S1004))
gt; gt;
gt; gt;gt; Hi experts,
gt; gt;gt;
gt; gt;[quoted text clipped - 12 lines]
gt; gt;gt; Thanks,
gt; gt;gt; Pierre
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...tions/200601/1