Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Formula in Excel with two conditions
Okay I give up! I have tried COUNTIF, SUMPRODUCT, IF, COUNT, and I cannot
get this to work. I have a spreadsheet that if the following two criteria are met I want to count it. Column D - "ASA-2" Column G - "2" I am counting how many of ASA-2 (customer identifier-column D) entries have Level 2 issues (column G). I have looked throught many of the postings and the formulas keep giving me 0 or NAME# error. Yes I did Ctrl-Shift-Enter. Still nothing. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Formula in Excel with two conditions
SUMPRODUCT is probably the function you would want in this case.
=SUMPRODUCT(--(D1:D10="ASA-2"),--(G1:G10="2")) Where I foresee that you may run into a problem is with Column G. In your example you're looking for the text value 2. Is the data in Column G in fact stored as text? Or is it numbers? 2 and "2" are not the same thing. Remove the quotes from the "2" if your data is stored as numbers. HTH, Elkar "Gayla" wrote: Okay I give up! I have tried COUNTIF, SUMPRODUCT, IF, COUNT, and I cannot get this to work. I have a spreadsheet that if the following two criteria are met I want to count it. Column D - "ASA-2" Column G - "2" I am counting how many of ASA-2 (customer identifier-column D) entries have Level 2 issues (column G). I have looked throught many of the postings and the formulas keep giving me 0 or NAME# error. Yes I did Ctrl-Shift-Enter. Still nothing. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Formula in Excel with two conditions
Hi,
This is how you can solve the problem using array formulas (Ctrl+Shift+Enter) The following data is in range D5:E7 ASA-2 2 ASA-3 3 ASA-2 2 In cell E9, array enter the following formula =SUM(IF(($D$5:$D$7=D5)*(E5:E7=E5),1,0)) Regards, Ashish Mathur "Gayla" wrote: Okay I give up! I have tried COUNTIF, SUMPRODUCT, IF, COUNT, and I cannot get this to work. I have a spreadsheet that if the following two criteria are met I want to count it. Column D - "ASA-2" Column G - "2" I am counting how many of ASA-2 (customer identifier-column D) entries have Level 2 issues (column G). I have looked throught many of the postings and the formulas keep giving me 0 or NAME# error. Yes I did Ctrl-Shift-Enter. Still nothing. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Formula in Excel with two conditions
I suggest you to use Conditional Sum Wizard to solve the problem. But it will
work best if you need the result only in one cells. For instance, you have 2-columns and thousand row and you want to count howmany ASA-2 has level 2. By default, you will not find Conditional Sum Wizard in Tools menu. First you have to use Add-in from Tools menu to Add Conditional Sum Wizard. After that it will appear in Tools menu. Actually you have to sum the Column D (Customer Identifier) and will work like Count. An example when you will start Conditional Sum: List Range: sheet1!$d$1:$e$7 Column to Sum: Identity Column: Is: This Value: Group = 2 (then second condition) Identity = ASA-2 Conditional Sum will auto generate a formula for you. But the only problem is that you can not copy the formula. You have to use CS for every condition. Hope this information helps you, let us know! "Gayla" wrote: Okay I give up! I have tried COUNTIF, SUMPRODUCT, IF, COUNT, and I cannot get this to work. I have a spreadsheet that if the following two criteria are met I want to count it. Column D - "ASA-2" Column G - "2" I am counting how many of ASA-2 (customer identifier-column D) entries have Level 2 issues (column G). I have looked throught many of the postings and the formulas keep giving me 0 or NAME# error. Yes I did Ctrl-Shift-Enter. Still nothing. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
an excel formula for specific conditions | Excel Worksheet Functions | |||
multiple conditions in COUNTIF (Excel 2000) | Excel Discussion (Misc queries) | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions |