software

Hi all,

With the help of the sumproduct formula I'm trying to count how many tickets
were created by our Servicedesk team sorted by division of the reporter of
the incident.

In my spreadsheet Column N shows the groups that can create tickets and
column T shows the division of the reporter of the ticket.

I used this formula:

=SUMPRODUCT(('Data SD Opened'!N2:N65536=quot;dlo-NL-HDK*quot;)*('Data SD Opened'!T2:
T65536=quot;*Concernstaf*quot;))

The result I get is 0 even though there are tickets from the division
Concernstaf.

Can somebody please tell me what's wrong in my formula?

Any help would be greatly appreciated.

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

=SUMPRODUCT(--(LEFT('Data SD
Opened'!N2:N65536,10)=quot;dlo-NL-HDKquot;)--(ISNUMBER(MATCH(quot;Concernstafquot;,'Data SD
Opened'!T2:T65536))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;RJS76 via OfficeKB.comquot; lt;u15953@uwegt; wrote in message
news:5b394467c42d6@uwe...
gt; Hi all,
gt;
gt; With the help of the sumproduct formula I'm trying to count how many
tickets
gt; were created by our Servicedesk team sorted by division of the reporter of
gt; the incident.
gt;
gt; In my spreadsheet Column N shows the groups that can create tickets and
gt; column T shows the division of the reporter of the ticket.
gt;
gt; I used this formula:
gt;
gt; =SUMPRODUCT(('Data SD Opened'!N2:N65536=quot;dlo-NL-HDK*quot;)*('Data SD
Opened'!T2:
gt; T65536=quot;*Concernstaf*quot;))
gt;
gt; The result I get is 0 even though there are tickets from the division
gt; Concernstaf.
gt;
gt; Can somebody please tell me what's wrong in my formula?
gt;
gt; Any help would be greatly appreciated.
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...tions/200602/1
Thanks for your reply.

When I use your formula I get the same result (0). When I filter the
spreadsheet for Concernstaf it shows me 8 results.

Any other suggestions?Bob Phillips wrote:
gt;=SUMPRODUCT(--(LEFT('Data SD
gt;Opened'!N2:N65536,10)=quot;dlo-NL-HDKquot;)--(ISNUMBER(MATCH(quot;Concernstafquot;,'Data SD
gt;Opened'!T2:T65536))))
gt;
gt;gt; Hi all,
gt;gt;
gt;[quoted text clipped - 16 lines]
gt;gt;
gt;gt; Any help would be greatly appreciated.

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


Bob,

I presume you meant to use SEARCH not MATCH

=SUMPRODUCT(--(LEFT('Data SD
Opened'!N2:N65536,10)=quot;dlo-NL-HDKquot;)--ISNUMBER(SEARCH(quot;Concernstafquot;,'Data
SD Opened'!T2:T65536)))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=507150
Hi, did you try my version, unfortunately managed to insert my own typo
(missing comma). The edited version above should work for you

=SUMPRODUCT(--(LEFT('Data SD
Opened'!N2:N65536,10)=quot;dlo-NL-HDKquot;),--ISNUMBER(SEARCH(quot;Concernstafquot;,'Data
SD Opened'!T2:T65536)))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=507150Thanks, that did the trick!

daddylonglegs wrote:
gt;Bob,
gt;
gt;I presume you meant to use SEARCH not MATCH
gt;
gt;=SUMPRODUCT(--(LEFT('Data S
gt;Opened'!N2:N65536,10)=quot;dlo-NL-HDKquot;)--ISNUMBER(SEARCH(quot;Concernstafquot;,'Dat
gt;SD Opened'!T2:T65536))

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