CountIf and Search
 

Hi guys me again :D

I have a set of columns say

incident number summary Assignee
118487 **ASG** 1
118456 ASG 1
115337 ASG - Test 2
115674 bobs ASG 3
123585 test 1
116897 drum 2
123585 ASG 1


OK now i want to do a count statement that will search the Summary for
anything with ASG in it so it needs to count **ASG** and ASG - Test etc as
the same thing but also only out it if it has a match to the assignee field
so say count all ASG for assignee 1

so in this i need it to return the value 3 in the above example

CountIf and Search
 

Hi

Assuming those values are in A1:C8 (headers in A1) then you cn use:

=SUMPRODUCT(($C$2:$C$8=1)+0,ISNUMBER(SEARCH("ASG", $B$2:$B$8))+0)

Hope this helps!

Richard



On 9 Mar, 10:42, Gareth - Network analyst.
wrote:
Hi guys me again :D

I have a set of columns say

incident number summary Assignee
118487 **ASG** 1
118456 ASG 1
115337 ASG - Test 2
115674 bobs ASG 3
123585 test 1
116897 drum 2
123585 ASG 1

OK now i want to do a count statement that will search the Summary for
anything with ASG in it so it needs to count **ASG** and ASG - Test etc as
the same thing but also only out it if it has a match to the assignee field
so say count all ASG for assignee 1

so in this i need it to return the value 3 in the above example

CountIf and Search
 

You can use a matrix formula to check conditions and sum when conditions are
true.
{=SUM(IF(ISERROR(SEARCH("ASG";B2:B8));0;1)*(D2:D8= 1))}

"Gareth - Network analyst." wrote:

Hi guys me again :D

I have a set of columns say

incident number summary Assignee
118487 **ASG** 1
118456 ASG 1
115337 ASG - Test 2
115674 bobs ASG 3
123585 test 1
116897 drum 2
123585 ASG 1


OK now i want to do a count statement that will search the Summary for
anything with ASG in it so it needs to count **ASG** and ASG - Test etc as
the same thing but also only out it if it has a match to the assignee field
so say count all ASG for assignee 1

so in this i need it to return the value 3 in the above example

CountIf and Search
 

Thanks Richard that seems to work :D and Arnaud for some reason your one
doesn't search for all the ASG arguments but thanks :D

"RichardSchollar" wrote:

Hi

Assuming those values are in A1:C8 (headers in A1) then you cn use:

=SUMPRODUCT(($C$2:$C$8=1)+0,ISNUMBER(SEARCH("ASG", $B$2:$B$8))+0)

Hope this helps!

Richard



On 9 Mar, 10:42, Gareth - Network analyst.
wrote:
Hi guys me again :D

I have a set of columns say

incident number summary Assignee
118487 **ASG** 1
118456 ASG 1
115337 ASG - Test 2
115674 bobs ASG 3
123585 test 1
116897 drum 2
123585 ASG 1

OK now i want to do a count statement that will search the Summary for
anything with ASG in it so it needs to count **ASG** and ASG - Test etc as
the same thing but also only out it if it has a match to the assignee field
so say count all ASG for assignee 1

so in this i need it to return the value 3 in the above example

CountIf and Search
 

Arg ok problem 2 now

same columns but

Incident+ (column 1)
112123
213123
546213
789456
546213

Summary+ (column 2)
**ASG**
ASG
ASG - BOB
test
dump

Assignee+ (column 3)
Gareth Thompson
Gareth Thompson
Bob Dilan
Ross Flake
Gareth Thompson
Gareth Thompson

All this information is on Sheet 1. Now my control sheet is on sheet2

So my sheet two looks like this.
Gareth Has - (formula in next cell) - ASG calls
Bob Dilan - (formula in next cell) - ASG calls
Ross Flake - (formula in next cell) - ASG calls

Now having Sumformula and Sum wont work right??? or how would i do this???

CountIf and Search
 

Gareth

So on sheet2 you want something like:

=SUMPRODUCT((Sheet1!$C$2:$C$8=Sheet2!
$A2)+0,ISNUMBER(SEARCH("ASG",Sheet1!$B$2:$B$8))+0)

copied down?

Richard


On 9 Mar, 12:11, Gareth - Network analyst.
wrote:
Arg ok problem 2 now

same columns but

Incident+ (column 1)
112123
213123
546213
789456
546213

Summary+ (column 2)
**ASG**
ASG
ASG - BOB
test
dump

Assignee+ (column 3)
Gareth Thompson
Gareth Thompson
Bob Dilan
Ross Flake
Gareth Thompson
Gareth Thompson

All this information is on Sheet 1. Now my control sheet is on sheet2

So my sheet two looks like this.
Gareth Has - (formula in next cell) - ASG calls
Bob Dilan - (formula in next cell) - ASG calls
Ross Flake - (formula in next cell) - ASG calls

Now having Sumformula and Sum wont work right??? or how would i do this???

CountIf and Search
 

AHHHHHHHHHHHHHH :D thanks again :D it worked :D just got to add a value to
look up against :D

thanks Richard

"RichardSchollar" wrote:

Gareth

So on sheet2 you want something like:

=SUMPRODUCT((Sheet1!$C$2:$C$8=Sheet2!
$A2)+0,ISNUMBER(SEARCH("ASG",Sheet1!$B$2:$B$8))+0)

copied down?

Richard


On 9 Mar, 12:11, Gareth - Network analyst.
wrote:
Arg ok problem 2 now

same columns but

Incident+ (column 1)
112123
213123
546213
789456
546213

Summary+ (column 2)
**ASG**
ASG
ASG - BOB
test
dump

Assignee+ (column 3)
Gareth Thompson
Gareth Thompson
Bob Dilan
Ross Flake
Gareth Thompson
Gareth Thompson

All this information is on Sheet 1. Now my control sheet is on sheet2

So my sheet two looks like this.
Gareth Has - (formula in next cell) - ASG calls
Bob Dilan - (formula in next cell) - ASG calls
Ross Flake - (formula in next cell) - ASG calls

Now having Sumformula and Sum wont work right??? or how would i do this???