Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIFS Formula Problem
Has anyone experienced any substantial errors from the Countifs Formula?
A B C 1 15 51 30 3 20 30 18 4 20 15 18 5 15 51 30 COUNTIFS(A3:C3,A1,A4:C4, B1,A5:C5, C1) = 0 when it should equal 1 Is my syntax wrong? This what the formula produces? Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIFS Formula Problem
I haven't quite figured out any usefulness to the new COUNTIFS function yet.
It doesn't work the way you'd think. If all 3 conditions were met in a particular spot in the array, then it would count 1. So, if your data looked like: 15 51 30 15 30 18 51 15 18 30 51 30 Then it would return a value of 1, since A3, A4, and A5 all met their respective criteria. I've gotten used to using SUMPRODUCT for this type of problem, so haven't really seen much need for these new functions. =SUMPRODUCT((A3:C3=A1)+(A4:C4=B1)+(A5:C5=C1)) HTH, Elkar "Jim" wrote: Has anyone experienced any substantial errors from the Countifs Formula? A B C 1 15 51 30 3 20 30 18 4 20 15 18 5 15 51 30 COUNTIFS(A3:C3,A1,A4:C4, B1,A5:C5, C1) = 0 when it should equal 1 Is my syntax wrong? This what the formula produces? Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIFS Formula Problem
Thanks for the info on counifs and the alternative suggestion. This should
solve my need, I hope. The instuctions for countifs certainly certianly don't make clear the need for all conditions to be met as you have explained and therefor are a bit misleading I think. Again thanks for the help. "Elkar" wrote: I haven't quite figured out any usefulness to the new COUNTIFS function yet. It doesn't work the way you'd think. If all 3 conditions were met in a particular spot in the array, then it would count 1. So, if your data looked like: 15 51 30 15 30 18 51 15 18 30 51 30 Then it would return a value of 1, since A3, A4, and A5 all met their respective criteria. I've gotten used to using SUMPRODUCT for this type of problem, so haven't really seen much need for these new functions. =SUMPRODUCT((A3:C3=A1)+(A4:C4=B1)+(A5:C5=C1)) HTH, Elkar "Jim" wrote: Has anyone experienced any substantial errors from the Countifs Formula? A B C 1 15 51 30 3 20 30 18 4 20 15 18 5 15 51 30 COUNTIFS(A3:C3,A1,A4:C4, B1,A5:C5, C1) = 0 when it should equal 1 Is my syntax wrong? This what the formula produces? Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIFS Formula Problem
I have tested Elkar's suggestion and it provides the desired calculations.
It should be noted that the syntax required by the formula in Excel is very different and can not be efficiently used to set this formula up. "Jim" wrote: Thanks for the info on counifs and the alternative suggestion. This should solve my need, I hope. The instuctions for countifs certainly certianly don't make clear the need for all conditions to be met as you have explained and therefor are a bit misleading I think. Again thanks for the help. "Elkar" wrote: I haven't quite figured out any usefulness to the new COUNTIFS function yet. It doesn't work the way you'd think. If all 3 conditions were met in a particular spot in the array, then it would count 1. So, if your data looked like: 15 51 30 15 30 18 51 15 18 30 51 30 Then it would return a value of 1, since A3, A4, and A5 all met their respective criteria. I've gotten used to using SUMPRODUCT for this type of problem, so haven't really seen much need for these new functions. =SUMPRODUCT((A3:C3=A1)+(A4:C4=B1)+(A5:C5=C1)) HTH, Elkar "Jim" wrote: Has anyone experienced any substantial errors from the Countifs Formula? A B C 1 15 51 30 3 20 30 18 4 20 15 18 5 15 51 30 COUNTIFS(A3:C3,A1,A4:C4, B1,A5:C5, C1) = 0 when it should equal 1 Is my syntax wrong? This what the formula produces? Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIFS Formula Problem
On Sep 18, 5:38 pm, Jim wrote:
Has anyone experienced any substantial errors from theCountifsFormula? A B C 1 15 51 30 3 20 30 18 4 20 15 18 5 15 51 30 COUNTIFS(A3:C3,A1,A4:C4, B1,A5:C5, C1) = 0 when it should equal 1 Is my syntax wrong? This what the formula produces? Any ideas? I have a similar problem, which I thought I posted earlier today. I have the following cells: a b c 1 2/15/08 countifs(a1,""&today(),b1,"="&"") Cell c1 returns 0 instead of 1. Any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIFS Formula Problem
Hi
Try =COUNTIFS(A1,""&TODAY(),B1,"") -- Regards Roger Govier "Felsa Satlow" wrote in message ... On Sep 18, 5:38 pm, Jim wrote: Has anyone experienced any substantial errors from theCountifsFormula? A B C 1 15 51 30 3 20 30 18 4 20 15 18 5 15 51 30 COUNTIFS(A3:C3,A1,A4:C4, B1,A5:C5, C1) = 0 when it should equal 1 Is my syntax wrong? This what the formula produces? Any ideas? I have a similar problem, which I thought I posted earlier today. I have the following cells: a b c 1 2/15/08 countifs(a1,""&today(),b1,"="&"") Cell c1 returns 0 instead of 1. Any ideas? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIFS Formula Problem
Thanks, Roger, that worked beautifully.
Felsa On Nov 17, 11:49 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Try =COUNTIFS(A1,""&TODAY(),B1,"") -- Regards Roger Govier "Felsa Satlow" wrote in message ... On Sep 18, 5:38 pm, Jim wrote: Has anyone experienced any substantial errors from theCountifsFormula? A B C 1 15 51 30 3 20 30 18 4 20 15 18 5 15 51 30 COUNTIFS(A3:C3,A1,A4:C4, B1,A5:C5, C1) = 0 when it should equal 1 Is my syntax wrong? This what the formula produces? Any ideas? I have a similar problem, which I thought I posted earlier today. I have the following cells: a b c 1 2/15/08 countifs(a1,""&today(),b1,"="&"") Cell c1 returns 0 instead of 1. Any ideas?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countifs | Excel Discussion (Misc queries) | |||
PLS HLP! Countifs Syntax Problem? | Excel Worksheet Functions | |||
Averageifs & Countifs | Excel Worksheet Functions | |||
2 COUNTIFS | Excel Discussion (Misc queries) | |||
Multiple countifs | Excel Worksheet Functions |