Excel Formula for Count the Product and Last date of entry

[Vishaal]

Hi, we have the following table where we want some results
1- total count of data (as result sheet)
2-Last date of entry for the product

Data

Sno	Date	P Code	C Name
1	25.06.2015	256D	PDFDFP
2	26.06.2015	65D	FSFDSFD
3	26.06.2015	325D	DFSFS
4	28.06.2015	256D	PDFDFP
5	30.06.2015	658D	HJGHKGK
6	01.07.2015	965D	RYTRYY
7	02.07.2015	745D	FJJFGJ
8	02.07.2015	852D	JJTRE
9	03.07.2015	256D	PDFDFP
10	03.07.2015	325D	DFSFS
11	03.07.2015	658D	HJGHKGK
12	03.07.2015	745D	FJJFGJ
13	06.07.2015	658D	HJGHKGK
14	06.07.2015	852D	JJTRE

Result Data (you can show this data on same sheet or sheet2)

P Code	Count	Date
256D	3	03.07.2015
65D	1	26.06.2015
325D	2	03.07.2015
658D	3	06.07.2015
965D	1	01.07.2015
745D	2	03.07.2015
852D	2	06.07.2015

Kindly provide the excel formula

 

[etaf]

you are showing versions of excel as 2007 and 2010 - is that what you are using , there are a lot of functions in later versions to help do this

MAXIFS()

UNIQUE()

also the dates are they real dates excel recognises or text
right click on the date cell - and select general - you should see a number and not the date

 

[Vishaal]

you are showing versions of excel as 2007 and 2010 - is that what you are using , there are a lot of functions in later versions to help do this

MAXIFS()

UNIQUE()

also the dates are they real dates excel recognises or text
right click on the date cell - and select general - you should see a number and not the date

excel version is 2007 and dates are real, its a demo data so typed it manually
we will take care about that - date in excel

 

[etaf]

ok, and the date question ?

 

[Vishaal]

ok, and the date question ?

sorry, date format

we will post date as per excel format from next time in demo data also

thanks etaf, kindly provide the any solution

 

[etaf]

you can use
=COUNTIF($C$2:$C$15,G3)
to get the count
and
=MAX(IF($C$2:$C$15=G3,$B$2:$B$15)) - using Control+shift+enter to get an array {} around the formula for date
Assuming the date is actually a date and NOT text

i have added to column K & L for you to see an compare

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Sno	Date	P Code	C Name
2	1	6/25/15	256D	PDFDFP			P Code	Count	Date
3	2	6/26/15	65D	FSFDSFD			256D	3	03.07.2015		3	7/3/15
4	3	6/26/15	325D	DFSFS			65D	1	26.06.2015		1	6/26/15
5	4	6/28/15	256D	PDFDFP			325D	2	03.07.2015		2	7/3/15
6	5	6/30/15	658D	HJGHKGK			658D	3	06.07.2015		3	7/6/15
7	6	7/1/15	965D	RYTRYY			965D	1	01.07.2015		1	7/1/15
8	7	7/2/15	745D	FJJFGJ			745D	2	03.07.2015		2	7/3/15
9	8	7/2/15	852D	JJTRE			852D	2	06.07.2015		2	7/6/15
10	9	7/3/15	256D	PDFDFP
11	10	7/3/15	325D	DFSFS
12	11	7/3/15	658D	HJGHKGK
13	12	7/3/15	745D	FJJFGJ
14	13	7/6/15	658D	HJGHKGK
15	14	7/6/15	852D	JJTRE
16
Sheet1

Cell Formulas
Range	Formula
K3:K9	K3	=COUNTIF($C$2:$C$15,G3)
L3:L9	L3	=MAX(IF($C$2:$C$15=G3,$B$2:$B$15))