Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
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="dlo-NL-HDK*")*('Data SD Opened'!T2: T65536="*Concernstaf*")) 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 http://www.officekb.com/Uwe/Forums.a...tions/200602/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
=SUMPRODUCT(--(LEFT('Data SD
Opened'!N2:N65536,10)="dlo-NL-HDK")--(ISNUMBER(MATCH("Concernstaf",'Data SD Opened'!T2:T65536)))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "RJS76 via OfficeKB.com" <u15953@uwe wrote in message news:5b394467c42d6@uwe... 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="dlo-NL-HDK*")*('Data SD Opened'!T2: T65536="*Concernstaf*")) 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 http://www.officekb.com/Uwe/Forums.a...tions/200602/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
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: =SUMPRODUCT(--(LEFT('Data SD Opened'!N2:N65536,10)="dlo-NL-HDK")--(ISNUMBER(MATCH("Concernstaf",'Data SD Opened'!T2:T65536)))) Hi all, [quoted text clipped - 16 lines] Any help would be greatly appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200602/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
Bob, I presume you meant to use SEARCH not MATCH =SUMPRODUCT(--(LEFT('Data SD Opened'!N2:N65536,10)="dlo-NL-HDK")--ISNUMBER(SEARCH("Concernstaf",'Data SD Opened'!T2:T65536))) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=507150 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
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)="dlo-NL-HDK"),--ISNUMBER(SEARCH("Concernstaf",'Data SD Opened'!T2:T65536))) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=507150 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
Thanks, that did the trick!
daddylonglegs wrote: Bob, I presume you meant to use SEARCH not MATCH =SUMPRODUCT(--(LEFT('Data S Opened'!N2:N65536,10)="dlo-NL-HDK")--ISNUMBER(SEARCH("Concernstaf",'Dat SD Opened'!T2:T65536)) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200602/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT Question | Excel Worksheet Functions | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
another sumproduct question | Excel Worksheet Functions | |||
SUMPRODUCT Question | Excel Discussion (Misc queries) | |||
SUMPRODUCT Question... | Excel Discussion (Misc queries) |