Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIf Formula Help (2003)
I have been trying to work a formula to count cells based on certain
requirements. I am not sure if CountIf is the right formula to use.... I have named the following colums. N:N = Department U:U = Feature Z:Z = Calculation The formula I am trying to do is If Feature = path repair and Department = Concrete then count Calculation if it = Path Repair False. All the formulas I have tried to date have given me an error or an incorrect figure. I hope someone can help. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIf Formula Help (2003)
Try this
=SUMPRODUCT(--(Department="Concrete"),--(Feature="Path Repair"),Calculation) You need to define a range of rows for Department, Feature, Calculation like A1:A100 (and NOT A:A as you seem to have done)... Whole column reference might work in Excel 2007 ------------------------------------- Pl. click ''Yes'' if this was helpful... "cayang68" wrote: I have been trying to work a formula to count cells based on certain requirements. I am not sure if CountIf is the right formula to use.... I have named the following colums. N:N = Department U:U = Feature Z:Z = Calculation The formula I am trying to do is If Feature = path repair and Department = Concrete then count Calculation if it = Path Repair False. All the formulas I have tried to date have given me an error or an incorrect figure. I hope someone can help. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIf Formula Help (2003)
If you're using Excel 2003 then you can't use entire columns as range
references with this function. Use cells to hold your criteria: A1 = path repair B1 = Concrete C1 = Path Repair False =SUMPRODUCT(--(N1:N100=A1),--(U1:U100=B1),--(Z1:Z100=C1)) -- Biff Microsoft Excel MVP "cayang68" wrote in message ... I have been trying to work a formula to count cells based on certain requirements. I am not sure if CountIf is the right formula to use.... I have named the following colums. N:N = Department U:U = Feature Z:Z = Calculation The formula I am trying to do is If Feature = path repair and Department = Concrete then count Calculation if it = Path Repair False. All the formulas I have tried to date have given me an error or an incorrect figure. I hope someone can help. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIf Formula Help (2003)
Sorry, This does not work. The value that came back was incorrect.
I have now defined a range of rows as suggested Thanks "Sheeloo" wrote: Try this =SUMPRODUCT(--(Department="Concrete"),--(Feature="Path Repair"),Calculation) You need to define a range of rows for Department, Feature, Calculation like A1:A100 (and NOT A:A as you seem to have done)... Whole column reference might work in Excel 2007 ------------------------------------- Pl. click ''Yes'' if this was helpful... "cayang68" wrote: I have been trying to work a formula to count cells based on certain requirements. I am not sure if CountIf is the right formula to use.... I have named the following colums. N:N = Department U:U = Feature Z:Z = Calculation The formula I am trying to do is If Feature = path repair and Department = Concrete then count Calculation if it = Path Repair False. All the formulas I have tried to date have given me an error or an incorrect figure. I hope someone can help. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIf Formula Help (2003)
Thank You. works well
"T. Valko" wrote: If you're using Excel 2003 then you can't use entire columns as range references with this function. Use cells to hold your criteria: A1 = path repair B1 = Concrete C1 = Path Repair False =SUMPRODUCT(--(N1:N100=A1),--(U1:U100=B1),--(Z1:Z100=C1)) -- Biff Microsoft Excel MVP "cayang68" wrote in message ... I have been trying to work a formula to count cells based on certain requirements. I am not sure if CountIf is the right formula to use.... I have named the following colums. N:N = Department U:U = Feature Z:Z = Calculation The formula I am trying to do is If Feature = path repair and Department = Concrete then count Calculation if it = Path Repair False. All the formulas I have tried to date have given me an error or an incorrect figure. I hope someone can help. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIf Formula Help (2003)
I thought you wanted to sum the third column based on matches on first two
cols... Glad you got the solution through Biff... "cayang68" wrote: Sorry, This does not work. The value that came back was incorrect. I have now defined a range of rows as suggested Thanks "Sheeloo" wrote: Try this =SUMPRODUCT(--(Department="Concrete"),--(Feature="Path Repair"),Calculation) You need to define a range of rows for Department, Feature, Calculation like A1:A100 (and NOT A:A as you seem to have done)... Whole column reference might work in Excel 2007 ------------------------------------- Pl. click ''Yes'' if this was helpful... "cayang68" wrote: I have been trying to work a formula to count cells based on certain requirements. I am not sure if CountIf is the right formula to use.... I have named the following colums. N:N = Department U:U = Feature Z:Z = Calculation The formula I am trying to do is If Feature = path repair and Department = Concrete then count Calculation if it = Path Repair False. All the formulas I have tried to date have given me an error or an incorrect figure. I hope someone can help. Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIf Formula Help (2003)
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "cayang68" wrote in message ... Thank You. works well "T. Valko" wrote: If you're using Excel 2003 then you can't use entire columns as range references with this function. Use cells to hold your criteria: A1 = path repair B1 = Concrete C1 = Path Repair False =SUMPRODUCT(--(N1:N100=A1),--(U1:U100=B1),--(Z1:Z100=C1)) -- Biff Microsoft Excel MVP "cayang68" wrote in message ... I have been trying to work a formula to count cells based on certain requirements. I am not sure if CountIf is the right formula to use.... I have named the following colums. N:N = Department U:U = Feature Z:Z = Calculation The formula I am trying to do is If Feature = path repair and Department = Concrete then count Calculation if it = Path Repair False. All the formulas I have tried to date have given me an error or an incorrect figure. I hope someone can help. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif for 2003 | Excel Worksheet Functions | |||
Countif with dates for vs 2003 | Excel Worksheet Functions | |||
countif for 2003 | Excel Discussion (Misc queries) | |||
COUNTIF Bug in Excel 2003 | Excel Worksheet Functions | |||
WHERE IS COUNTIF IN EXCEL 2003 | Excel Worksheet Functions |