Creating a formula
 

Hi,

I need to create a formula that will look in 2 different colums and return a
count.

Column A will contain data (usually numbers) relating to reference codes to
areas (ie Bristol will be 039, Stroud will be 038 etc)

Column G contains a decision relating to that particular row (eg Delete,
Compliant, For Validation)

I want to be able to create a formula that will count the positive results
if A=039 & G=Delete (and like wise if A=039 & G=Compliant etc.)

I can do get the formula to do the first bit (ie =countif(A2:A500,039)) but
I don't know who to get the second half of the formula in the same cell.

I'm not as knowledgable on complex formulas (well it's complex to me
anyway!) as the ones I use usually are the basic versions. It's probably very
simple, but I just can't seam to get my head around it.

Can anyone help?
--
Your advice is appreciated
:-)

Creating a formula
 

Try this:

=SUMPRODUCT((A1:A10="039")*(G1:G10="Delete"))

Adjust range references to suit your situation

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"JMP" wrote:

Hi,

I need to create a formula that will look in 2 different colums and return a
count.

Column A will contain data (usually numbers) relating to reference codes to
areas (ie Bristol will be 039, Stroud will be 038 etc)

Column G contains a decision relating to that particular row (eg Delete,
Compliant, For Validation)

I want to be able to create a formula that will count the positive results
if A=039 & G=Delete (and like wise if A=039 & G=Compliant etc.)

I can do get the formula to do the first bit (ie =countif(A2:A500,039)) but
I don't know who to get the second half of the formula in the same cell.

I'm not as knowledgable on complex formulas (well it's complex to me
anyway!) as the ones I use usually are the basic versions. It's probably very
simple, but I just can't seam to get my head around it.

Can anyone help?
--
Your advice is appreciated
:-)

Creating a formula
 

Try something like

=SUMPRODUCT(--($A$1:$A$8="039"),--($G$1:$G$8="Delete"))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"JMP" wrote in message
...
Hi,

I need to create a formula that will look in 2 different colums and return
a
count.

Column A will contain data (usually numbers) relating to reference codes
to
areas (ie Bristol will be 039, Stroud will be 038 etc)

Column G contains a decision relating to that particular row (eg Delete,
Compliant, For Validation)

I want to be able to create a formula that will count the positive results
if A=039 & G=Delete (and like wise if A=039 & G=Compliant etc.)

I can do get the formula to do the first bit (ie =countif(A2:A500,039))
but
I don't know who to get the second half of the formula in the same cell.

I'm not as knowledgable on complex formulas (well it's complex to me
anyway!) as the ones I use usually are the basic versions. It's probably
very
simple, but I just can't seam to get my head around it.

Can anyone help?
--
Your advice is appreciated
:-)