Home |
Search |
Today's Posts |
#1
|
|||
|
|||
CountIF
Hi,
I can't figure out the correct formula. Code Date Wrong Correct NC-01 12/05/05 0 1 NC-01 0 NC-01 0 NC-02 10/08/05 0 1 NC-02 12/08/05 1 NC-03 0 NC-03 0 NC-04 01/05/05 1 1 NC-05 30/05/05 1 1 The idea is, if the code has a date then it will give a count 1, otherwise 0. My formula in column C (Wrong column) is =IF(A2=A3,0,IF(B2="",0,1)). In this case, I should have a 4 Codes with dates. What sort of trick to use to give me the correct count? Please advise. Thanks in advance. Carlos |
#2
|
|||
|
|||
One way to try ..
Put in C2: = --AND(ISNUMBER(B2),B2<0) Copy down -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Carlos" wrote in message ... Hi, I can't figure out the correct formula. Code Date Wrong Correct NC-01 12/05/05 0 1 NC-01 0 NC-01 0 NC-02 10/08/05 0 1 NC-02 12/08/05 1 NC-03 0 NC-03 0 NC-04 01/05/05 1 1 NC-05 30/05/05 1 1 The idea is, if the code has a date then it will give a count 1, otherwise 0. My formula in column C (Wrong column) is =IF(A2=A3,0,IF(B2="",0,1)). In this case, I should have a 4 Codes with dates. What sort of trick to use to give me the correct count? Please advise. Thanks in advance. Carlos |
#3
|
|||
|
|||
Hi!
If you're using this as a helper formula and your true goal is to count unique codes that have a date: Entered as an array with the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF(B2:B100,MATCH(A2:A10,A2:A10,0 )),MATCH(A2:A10,A2:A10,0))0)) If that's not what you're trying to do: Enter this formula in C2 and copy down as needed: =IF(AND(A1<A2,B20),1,"") Note: A1 in the above formula is not a typo. Biff "Carlos" wrote in message ... Hi, I can't figure out the correct formula. Code Date Wrong Correct NC-01 12/05/05 0 1 NC-01 0 NC-01 0 NC-02 10/08/05 0 1 NC-02 12/08/05 1 NC-03 0 NC-03 0 NC-04 01/05/05 1 1 NC-05 30/05/05 1 1 The idea is, if the code has a date then it will give a count 1, otherwise 0. My formula in column C (Wrong column) is =IF(A2=A3,0,IF(B2="",0,1)). In this case, I should have a 4 Codes with dates. What sort of trick to use to give me the correct count? Please advise. Thanks in advance. Carlos |
#4
|
|||
|
|||
Thank you guys for quick reply.
Max's formula give me 5 counts. Biff's two formulas give me the correct answer I want. I never thought of using AND function. . great! Thanks again. Carlos "Biff" wrote: Hi! If you're using this as a helper formula and your true goal is to count unique codes that have a date: Entered as an array with the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF(B2:B100,MATCH(A2:A10,A2:A10,0 )),MATCH(A2:A10,A2:A10,0))0)) If that's not what you're trying to do: Enter this formula in C2 and copy down as needed: =IF(AND(A1<A2,B20),1,"") Note: A1 in the above formula is not a typo. Biff "Carlos" wrote in message ... Hi, I can't figure out the correct formula. Code Date Wrong Correct NC-01 12/05/05 0 1 NC-01 0 NC-01 0 NC-02 10/08/05 0 1 NC-02 12/08/05 1 NC-03 0 NC-03 0 NC-04 01/05/05 1 1 NC-05 30/05/05 1 1 The idea is, if the code has a date then it will give a count 1, otherwise 0. My formula in column C (Wrong column) is =IF(A2=A3,0,IF(B2="",0,1)). In this case, I should have a 4 Codes with dates. What sort of trick to use to give me the correct count? Please advise. Thanks in advance. Carlos |
#5
|
|||
|
|||
Biff/Max
Seems to be a problem using the formula C2 =IF(AND(A1<A2,B20),1,"") for some other rows. Say, what if the date for NC-01 is at B4 (rather than B2 previously), it doesn't give 1. Code Date Count NC-01 NC-01 NC-01 12/05/05 NC-02 10/08/05 1 NC-02 12/08/05 NC-03 NC-03 NC-04 01/05/05 1 NC-05 30/05/05 1 However, the longer formula (=SUM(N(FREQUENCY(IF(B2:B100,MATCH(A2:A10,A2:A10, 0)),MATCH(A2:A10,A2:A10,0))0)) works fine. Carlos "Carlos" wrote: Thank you guys for quick reply. Max's formula give me 5 counts. Biff's two formulas give me the correct answer I want. I never thought of using AND function. . great! Thanks again. Carlos "Biff" wrote: Hi! If you're using this as a helper formula and your true goal is to count unique codes that have a date: Entered as an array with the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF(B2:B100,MATCH(A2:A10,A2:A10,0 )),MATCH(A2:A10,A2:A10,0))0)) If that's not what you're trying to do: Enter this formula in C2 and copy down as needed: =IF(AND(A1<A2,B20),1,"") Note: A1 in the above formula is not a typo. Biff "Carlos" wrote in message ... Hi, I can't figure out the correct formula. Code Date Wrong Correct NC-01 12/05/05 0 1 NC-01 0 NC-01 0 NC-02 10/08/05 0 1 NC-02 12/08/05 1 NC-03 0 NC-03 0 NC-04 01/05/05 1 1 NC-05 30/05/05 1 1 The idea is, if the code has a date then it will give a count 1, otherwise 0. My formula in column C (Wrong column) is =IF(A2=A3,0,IF(B2="",0,1)). In this case, I should have a 4 Codes with dates. What sort of trick to use to give me the correct count? Please advise. Thanks in advance. Carlos |
#6
|
|||
|
|||
Max's formula give me 5 counts.
First, my apologies for missing the significance of your line: ... I should have a 4 Codes with dates ... which inferred that the counts were to be for unique codes A non-array alternative to try would be to put: In C2: =IF(AND(ISNUMBER(B2),B2<0),A2,"") In D2: = --(AND(C2<"",COUNTIF($C$2:C2,C2)=1)) Then select C2:D2 and fill down Col D will return the count results that you seek -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
|
|||
|
|||
Max,
Thanks, thanks.... and thanks. That's exactly what I need. Just wonder what does the two dashes "--" means in D2 formula? Rgrds Carlos "Max" wrote: Max's formula give me 5 counts. First, my apologies for missing the significance of your line: ... I should have a 4 Codes with dates ... which inferred that the counts were to be for unique codes A non-array alternative to try would be to put: In C2: =IF(AND(ISNUMBER(B2),B2<0),A2,"") In D2: = --(AND(C2<"",COUNTIF($C$2:C2,C2)=1)) Then select C2:D2 and fill down Col D will return the count results that you seek -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#8
|
|||
|
|||
Glad to hear it's of some help <g !
.. what does the two dashes "--" mean in D2 formula? The "--" is what they call a double unary which coerces the TRUE / FALSE returns in the comparisons evaluated within the parens to numeric 1's / 0's -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Carlos" wrote in message ... Max, Thanks, thanks.... and thanks. That's exactly what I need. Just wonder what does the two dashes "--" means in D2 formula? Rgrds Carlos |
#9
|
|||
|
|||
Mmmm..... i guess i don't need to know it further and seems i have a lot more
things to know about Excel. Thanks again. God bless. Carlos "Max" wrote: Glad to hear it's of some help <g ! .. what does the two dashes "--" mean in D2 formula? The "--" is what they call a double unary which coerces the TRUE / FALSE returns in the comparisons evaluated within the parens to numeric 1's / 0's -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Carlos" wrote in message ... Max, Thanks, thanks.... and thanks. That's exactly what I need. Just wonder what does the two dashes "--" means in D2 formula? Rgrds Carlos |
#10
|
|||
|
|||
You're welcome, Carlos !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |