counting/numbering with 2 or more colums?

[atanatashi]

Hi, is it possible to count/numbering with two columns as its range?

i wanted a numbering on the dates as shown below;
the countif formula works but on one column only, how can i add the other column so it does not only count the date 1 column but also the date 2 column where the answer should be the bold numbers. thanks

[TABLE="width: 439"]
<tbody>[TR]
[TD][/TD]
[TD]#[/TD]
[TD]DATE 1[/TD]
[TD]DATA[/TD]
[TD]#[/TD]
[TD]DATE 2[/TD]
[TD]DATA[/TD]
[/TR]
[TR]
[TD="align: left"]COUNTIF($C$2:C2,C2)[/TD]
[TD]1[/TD]
[TD]7/4/2014[/TD]
[TD]XXXX[/TD]
[TD]5[/TD]
[TD]7/4/2014[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD="align: left"]COUNTIF($C$2:C3,C3)[/TD]
[TD]2[/TD]
[TD]7/4/2014[/TD]
[TD]XXXX[/TD]
[TD]2[/TD]
[TD]7/7/2014[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD="align: left"]COUNTIF($C$2:C4,C4)[/TD]
[TD]3[/TD]
[TD]7/4/2014[/TD]
[TD]XXXX[/TD]
[TD]1[/TD]
[TD]7/8/2014[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]7/4/2014[/TD]
[TD]XXXX[/TD]
[TD]2[/TD]
[TD]7/8/2014[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]7/7/2014[/TD]
[TD]XXXX[/TD]
[TD]2[/TD]
[TD]7/9/2014[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]7/9/2014[/TD]
[TD]XXXX[/TD]
[TD]3[/TD]
[TD]7/9/2014[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]7/10/2014[/TD]
[TD]XXXX[/TD]
[TD]2[/TD]
[TD]7/10/2014[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]7/11/2014[/TD]
[TD]XXXX[/TD]
[TD]3[/TD]
[TD]7/11/2014[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]7/11/2014[/TD]
[TD]XXXX[/TD]
[TD]4[/TD]
[TD]7/11/2014[/TD]
[TD]XXXX[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dharris144]

Countif($c$2:$G$10,c2)

include the whole range and it will add them all

 

[atanatashi]

Hi, thanks for the reply. if I include the whole range it will count the criteria in C2 which is 7/4/2014 which results to 5.
what i need is to create a numbering sequence, where it will counts the number of occurrence every time the criteria is met. example from ($C$2:C2,C2) it will count first, ($C$2:C3,C3) it will count second, ($C$2:C4,C4) will count third so on and so forth since the criteria is still the same, till a new criteria is met which in this case ($C$2:C6,C6) it will count 1 since that's the first 7/7/2014 on the range. doing this in one column of range gives me the correct counting/numbering, i don't know how to do it in 2 or more columns. is this possible? thanks again.

 

[Marcelo Branco]

Not sure if I understand what you need

See if this is Ok


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

#​

[/td][td]

DATE 1​

[/td][td]

DATA​

[/td][td]

#​

[/td][td]

DATE 2​

[/td][td]

DATA​

[/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td]

07/04/2014​

[/td][td]

XXXX​

[/td][td]

5​

[/td][td]

07/04/2014​

[/td][td]

XXXX​

[/td][/tr]
[tr][td]

3​

[/td][td]

2​

[/td][td]

07/04/2014​

[/td][td]

XXXX​

[/td][td]

2​

[/td][td]

07/07/2014​

[/td][td]

XXXX​

[/td][/tr]
[tr][td]

4​

[/td][td]

3​

[/td][td]

07/04/2014​

[/td][td]

XXXX​

[/td][td]

1​

[/td][td]

07/08/2014​

[/td][td]

XXXX​

[/td][/tr]
[tr][td]

5​

[/td][td]

4​

[/td][td]

07/04/2014​

[/td][td]

XXXX​

[/td][td]

2​

[/td][td]

07/08/2014​

[/td][td]

XXXX​

[/td][/tr]
[tr][td]

6​

[/td][td]

1​

[/td][td]

07/07/2014​

[/td][td]

XXXX​

[/td][td]

2​

[/td][td]

07/09/2014​

[/td][td]

XXXX​

[/td][/tr]
[tr][td]

7​

[/td][td]

1​

[/td][td]

07/09/2014​

[/td][td]

XXXX​

[/td][td]

3​

[/td][td]

07/09/2014​

[/td][td]

XXXX​

[/td][/tr]
[tr][td]

8​

[/td][td]

1​

[/td][td]

07/10/2014​

[/td][td]

XXXX​

[/td][td]

2​

[/td][td]

07/10/2014​

[/td][td]

XXXX​

[/td][/tr]
[tr][td]

9​

[/td][td]

1​

[/td][td]

07/11/2014​

[/td][td]

XXXX​

[/td][td]

3​

[/td][td]

07/11/2014​

[/td][td]

XXXX​

[/td][/tr]
[tr][td]

10​

[/td][td]

2​

[/td][td]

07/11/2014​

[/td][td]

XXXX​

[/td][td]

4​

[/td][td]

07/11/2014​

[/td][td]

XXXX​

[/td][/tr]
[/table]

Formula in A2 copied down
=COUNTIF(B$2:B2,B2)

Formula in D2 copied down
=COUNTIF(E$2:E2,E2)+COUNTIF($B$2:$B$10,E2)

Hope this helps

M.

 

[atanatashi]

oMYGlob! it works! thank you so much Marco! i tried adding 2 countifs before but got the ranges wrong.. thanks again this is a big help.

 

[Marcelo Branco]

oMYGlob! it works! thank you so much Marco! i tried adding 2 countifs before but got the ranges wrong.. thanks again this is a big help.

You are welcome and thanks for the feedback

M.