| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells that match 2 criteria
I have serveral columns of Data. In Col B some rows contain "PC". Col F
contains a forumla that returns TRUE or FALSE. I want to count the number of times "PC" appears in col B but only when FALSE is in col F. I have used this type of formula many times but only when there is just text in col F ie =SUMPRODUCT((FINAL1!B2:BY5000="PC")*(FINAL1!F2:F50 00="Customer")) The formula in col F is =AND(B2="PC",D2="PC") I would be grateful for any help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting cells that match 2 criteria
try just adding the parameter ie:
=SUMPRODUCT((B9:B12="a")*(G9:G12=FALSE)) -- Don Guillett SalesAid Software "jimar" wrote in message ... I have serveral columns of Data. In Col B some rows contain "PC". Col F contains a forumla that returns TRUE or FALSE. I want to count the number of times "PC" appears in col B but only when FALSE is in col F. I have used this type of formula many times but only when there is just text in col F ie =SUMPRODUCT((FINAL1!B2:BY5000="PC")*(FINAL1!F2:F50 00="Customer")) The formula in col F is =AND(B2="PC",D2="PC") I would be grateful for any help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting cells that match 2 criteria
On Feb 7, 10:31 am, "Don Guillett" wrote:
try just adding the parameter ie: =SUMPRODUCT((B9:B12="a")*(G9:G12=FALSE)) -- Don Guillett SalesAid Software "jimar" wrote in message ... I have serveral columns of Data. In Col B some rows contain "PC". Col F contains a forumla that returns TRUE or FALSE. I want to count the number of times "PC" appears in col B but only when FALSE is in col F. I have used this type of formula many times but only when there is just text in col F ie =SUMPRODUCT((FINAL1!B2:BY5000="PC")*(FINAL1!F2:F50 00="Customer")) The formula in col F is =AND(B2="PC",D2="PC") I would be grateful for any help.- Hide quoted text - - Show quoted text - Did you ever try using the COUNTIF function? I find that function very handy when I want to count the number of times that "particular" cell content excists. However this is a very slow calculation if you have many lines, like 2000+. Frode |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting cells that match 2 criteria
|
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting cells that match 2 criteria
Glad to help
-- Don Guillett SalesAid Software "jimar" wrote in message ... Thanks to both of you for your replies. Sumproduct worked perfectly. (I had tried this formula previously but had put inverted comas around FALSE) "Don Guillett" wrote: Wouldn't work well here -- Don Guillett SalesAid Software "FrodeOlsen" wrote in message ups.com... On Feb 7, 10:31 am, "Don Guillett" wrote: try just adding the parameter ie: =SUMPRODUCT((B9:B12="a")*(G9:G12=FALSE)) -- Don Guillett SalesAid Software "jimar" wrote in message ... I have serveral columns of Data. In Col B some rows contain "PC". Col F contains a forumla that returns TRUE or FALSE. I want to count the number of times "PC" appears in col B but only when FALSE is in col F. I have used this type of formula many times but only when there is just text in col F ie =SUMPRODUCT((FINAL1!B2:BY5000="PC")*(FINAL1!F2:F50 00="Customer")) The formula in col F is =AND(B2="PC",D2="PC") I would be grateful for any help.- Hide quoted text - - Show quoted text - Did you ever try using the COUNTIF function? I find that function very handy when I want to count the number of times that "particular" cell content excists. However this is a very slow calculation if you have many lines, like 2000+. Frode |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating only non-empty cells... | Excel Worksheet Functions | |||
| counting cells with two criteria | Excel Discussion (Misc queries) | |||
| Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions | |||
| counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
| Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions | |||
Linear Mode
