Help Please - CountIf and Array formula
 

I'm struggling to get to grips with array formula and don't have much time
left and so I am asking for your help to programatically enter a fomula that
will count
the cells in Range(A2:A500) that contain String_A whose corresponding cell
in Range(N2:N500) contains String_B

My grateful thanks in advance for any help

Help Please - CountIf and Array formula
 

i believe this will do want you need,

=SUMPRODUCT((A2:A500="string_a")*(n2:n500="string_ 2"))

--


Gary


"Prickle" <prick@somewherecom wrote in message
...
I'm struggling to get to grips with array formula and don't have much time
left and so I am asking for your help to programatically enter a fomula that
will count
the cells in Range(A2:A500) that contain String_A whose corresponding cell
in Range(N2:N500) contains String_B

My grateful thanks in advance for any help

Help Please - CountIf and Array formula
 

range("a1").formula=
"=SUMPRODUCT((A2:A500="string_a")*(n2:n500="string _2"))"


--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i believe this will do want you need,

=SUMPRODUCT((A2:A500="string_a")*(n2:n500="string_ 2"))

--


Gary


"Prickle" <prick@somewherecom wrote in message
...
I'm struggling to get to grips with array formula and don't have much
time
left and so I am asking for your help to programatically enter a fomula
that will count
the cells in Range(A2:A500) that contain String_A whose corresponding
cell
in Range(N2:N500) contains String_B

My grateful thanks in advance for any help

Help Please - CountIf and Array formula
 

Thanks to you both - works a treat

"Prickle" <prick@somewherecom wrote in message
...
I'm struggling to get to grips with array formula and don't have much time
left and so I am asking for your help to programatically enter a fomula
that will count
the cells in Range(A2:A500) that contain String_A whose corresponding cell
in Range(N2:N500) contains String_B

My grateful thanks in advance for any help

Help Please - CountIf and Array formula
 

If String_A and String_B are litterals that you are looking for

s = "=Sumproduct(--(A2:A500=""String_A""),--(N2:N500=""String_B""))
ActiveCell.Formula = s

if these are variables that contain string litterals

String_A = "a23rt"
String_B = "Johnson"
s = "=Sumproduct(--(A2:A500=""String_A""),--(N2:N500=""String_B""))
s = replace(s,"String_A",String_A)
s = replace(s,"String_B",String_B)
ActiveCell.Formula = s

--
Regards,
Tom Ogilvy


"Prickle" <prick@somewherecom wrote in message
...
I'm struggling to get to grips with array formula and don't have much time
left and so I am asking for your help to programatically enter a fomula
that
will count
the cells in Range(A2:A500) that contain String_A whose corresponding cell
in Range(N2:N500) contains String_B

My grateful thanks in advance for any help

Help Please - CountIf and Array formula
 

Don probably meant:

range("a1").formula= _
"=SUMPRODUCT((A2:A500=""string_a"")*(n2:n500=""str ing_2""))"

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
range("a1").formula=
"=SUMPRODUCT((A2:A500="string_a")*(n2:n500="string _2"))"


--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i believe this will do want you need,

=SUMPRODUCT((A2:A500="string_a")*(n2:n500="string_ 2"))

--


Gary


"Prickle" <prick@somewherecom wrote in message
...
I'm struggling to get to grips with array formula and don't have much
time
left and so I am asking for your help to programatically enter a fomula
that will count
the cells in Range(A2:A500) that contain String_A whose corresponding
cell
in Range(N2:N500) contains String_B

My grateful thanks in advance for any help

Help Please - CountIf and Array formula
 

Thanks for the catch Tom.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
Don probably meant:

range("a1").formula= _
"=SUMPRODUCT((A2:A500=""string_a"")*(n2:n500=""str ing_2""))"

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
range("a1").formula=
"=SUMPRODUCT((A2:A500="string_a")*(n2:n500="string _2"))"


--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i believe this will do want you need,

=SUMPRODUCT((A2:A500="string_a")*(n2:n500="string_ 2"))

--


Gary


"Prickle" <prick@somewherecom wrote in message
...
I'm struggling to get to grips with array formula and don't have much
time
left and so I am asking for your help to programatically enter a
fomula
that will count
the cells in Range(A2:A500) that contain String_A whose corresponding
cell
in Range(N2:N500) contains String_B

My grateful thanks in advance for any help