Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 :-) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 :-) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
Creating a complicated formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
need help creating formula based on cell value | Excel Discussion (Misc queries) | |||
Creating a specific formula | New Users to Excel |