Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF using multiple criteria
Hi I need to write a formula which counts the number of cells where multiple
criteria are met. For example, I need a countif statement (or equivelent) that will add up total cells where F:F = "specific name" AND C:C begins with "FLT" When I have tried it has run it as two seperate statements rather than like a filtered search, so the result is wildly wrong. Can anyone help please Many Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF using multiple criteria
=SUMORODUCT(--(F2;F1000=X1),--(LEFT(C2:C1000),3)="FLT"))
X1 contains you name. Unless you have XL2007, you cannot use whole columns as ranges. "TritaniumZero" wrote: Hi I need to write a formula which counts the number of cells where multiple criteria are met. For example, I need a countif statement (or equivelent) that will add up total cells where F:F = "specific name" AND C:C begins with "FLT" When I have tried it has run it as two seperate statements rather than like a filtered search, so the result is wildly wrong. Can anyone help please Many Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF using multiple criteria
SUMORODUCT? <vbg
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Toppers" wrote in message ... =SUMORODUCT(--(F2;F1000=X1),--(LEFT(C2:C1000),3)="FLT")) X1 contains you name. Unless you have XL2007, you cannot use whole columns as ranges. "TritaniumZero" wrote: Hi I need to write a formula which counts the number of cells where multiple criteria are met. For example, I need a countif statement (or equivelent) that will add up total cells where F:F = "specific name" AND C:C begins with "FLT" When I have tried it has run it as two seperate statements rather than like a filtered search, so the result is wildly wrong. Can anyone help please Many Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF using multiple criteria
Try
=SUMPRODUCT(--(LEFT(C2:C500,3)="FLT"),--(F2:F500=F2)) Regards Peter "TritaniumZero" wrote: Hi I need to write a formula which counts the number of cells where multiple criteria are met. For example, I need a countif statement (or equivelent) that will add up total cells where F:F = "specific name" AND C:C begins with "FLT" When I have tried it has run it as two seperate statements rather than like a filtered search, so the result is wildly wrong. Can anyone help please Many Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF using multiple criteria
...... must get round to doing this typing course ......!
"Bob Phillips" wrote: SUMORODUCT? <vbg -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Toppers" wrote in message ... =SUMORODUCT(--(F2;F1000=X1),--(LEFT(C2:C1000),3)="FLT")) X1 contains you name. Unless you have XL2007, you cannot use whole columns as ranges. "TritaniumZero" wrote: Hi I need to write a formula which counts the number of cells where multiple criteria are met. For example, I need a countif statement (or equivelent) that will add up total cells where F:F = "specific name" AND C:C begins with "FLT" When I have tried it has run it as two seperate statements rather than like a filtered search, so the result is wildly wrong. Can anyone help please Many Thanks in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF using multiple criteria
That will only get you making the mistakes quicker ... believe me, I know!
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Toppers" wrote in message ... ..... must get round to doing this typing course ......! "Bob Phillips" wrote: SUMORODUCT? <vbg -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Toppers" wrote in message ... =SUMORODUCT(--(F2;F1000=X1),--(LEFT(C2:C1000),3)="FLT")) X1 contains you name. Unless you have XL2007, you cannot use whole columns as ranges. "TritaniumZero" wrote: Hi I need to write a formula which counts the number of cells where multiple criteria are met. For example, I need a countif statement (or equivelent) that will add up total cells where F:F = "specific name" AND C:C begins with "FLT" When I have tried it has run it as two seperate statements rather than like a filtered search, so the result is wildly wrong. Can anyone help please Many Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif with multiple criteria | Excel Discussion (Misc queries) | |||
Countif Multiple Criteria | Excel Discussion (Misc queries) | |||
Using Countif with multiple criteria | Excel Worksheet Functions | |||
Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |