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

	#	DATE 1	DATA	#	DATE 2	DATA
COUNTIF($C$2:C2,C2)	1	7/4/2014	XXXX	5	7/4/2014	XXXX
COUNTIF($C$2:C3,C3)	2	7/4/2014	XXXX	2	7/7/2014	XXXX
COUNTIF($C$2:C4,C4)	3	7/4/2014	XXXX	1	7/8/2014	XXXX
	4	7/4/2014	XXXX	2	7/8/2014	XXXX
	1	7/7/2014	XXXX	2	7/9/2014	XXXX
	1	7/9/2014	XXXX	3	7/9/2014	XXXX
	1	7/10/2014	XXXX	2	7/10/2014	XXXX
	1	7/11/2014	XXXX	3	7/11/2014	XXXX
	2	7/11/2014	XXXX	4	7/11/2014	XXXX

<tbody>
</tbody>

 

[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

	A​	B​	C​	D​	E​	F​
1​	#​	DATE 1​	DATA​	#​	DATE 2​	DATA​
2​	1​	07/04/2014​	XXXX​	5​	07/04/2014​	XXXX​
3​	2​	07/04/2014​	XXXX​	2​	07/07/2014​	XXXX​
4​	3​	07/04/2014​	XXXX​	1​	07/08/2014​	XXXX​
5​	4​	07/04/2014​	XXXX​	2​	07/08/2014​	XXXX​
6​	1​	07/07/2014​	XXXX​	2​	07/09/2014​	XXXX​
7​	1​	07/09/2014​	XXXX​	3​	07/09/2014​	XXXX​
8​	1​	07/10/2014​	XXXX​	2​	07/10/2014​	XXXX​
9​	1​	07/11/2014​	XXXX​	3​	07/11/2014​	XXXX​
10​	2​	07/11/2014​	XXXX​	4​	07/11/2014​	XXXX​

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.