How to get the counts by datewise

[BharathBabu]

Hello,


I have a query with regards to Counts, where i am working on the productivity sheet..

In Sheet one has --


No. 1 2 3 4 5 6 7

Request: 45666 208956 201212 206878 207595 204568 203225

Date: 1-Dec-12 1-Dec-12 1-Dec-12 2-Dec-12 2-Dec-12 2-Dec-12 2-Dec-12

Silver 1 3 3 3 5

Gold 2 2


and Sheet 2 has

1-Dec-12 2-Dec-12

Total Req.count Total Req.Count

Silver 4 ? 11 ?

Gold 2 ? 2 ?


Please let us know how to arrive counts by datewise.

By Manually we can tell the answer that is

1st Dec 2012 ---> 2 Silver Counts & 1 Gold and

2nd Dec 2012 ----> 3 Silver Count & 1 Gold count..


But I want to know how to arrive those answers by using formula...

Please adivse


Thanks

Bharath

 

[NARAYANK991]

Hi Bharath ,


If you have Excel 2007 and later , you can use the COUNTIFS function :


=COUNTIFS(date_range,date,count_range,">1")


for the silver count , and


=COUNTIFS(date_range,date,count_range,"=1")


for the gold count.


A specific example is : =COUNTIFS($C$2:$C$11,F2,$D$2:$D$11,">1")


where F2 will have 1-Dec-12 as a date i.e. 12/1/2012 or 1/12/2012 or ... depending on whether your default date format is mm/dd/yyyy or dd/mm/yyyy or ...


Narayan

 

[BharathBabu]

I need the Count the number of cells which has silver and gold by different datewise.


Not able to get the answer...

 

[BharathBabu]

Hello,


I have a query with regards to Counts, where i am working on the productivity sheet..

In Sheet one has --


No. 1 2 3 4 5 6 7

Request: 45666 208956 201212 206878 207595 204568 203225

Date: 1-Dec-12 1-Dec-12 1-Dec-12 2-Dec-12 2-Dec-12 2-Dec-12 2-Dec-12

Silver: 1 3 3 3 5 0

Gold: 0 0 1 0 0 1


and Sheet 2 has

1-Dec-12 2-Dec-12

Total Req.count Total Req.Count

Silver 4 ? 11 ?

Gold 2 ? 2 ?


Please let us know how to arrive counts by datewise.

By Manually we can tell the answer that is

1st Dec 2012 ---> 2 Silver Counts & 1 Gold and

2nd Dec 2012 ----> 3 Silver Count & 1 Gold count..


But I want to know how to arrive those answers by using formula...

Please adivse


Thanks

Bharath

 

[Jai9]

Hi,


Not sure how you are getting the counts as you mentioned in the post.

When I tried, I got the below counts.


No. 1 2 3 4 5 6 7

Request:45666 208956 201212 206878 207595 204568 203225

Date: 1-Dec 1-Dec 1-Dec 2-Dec 2-Dec 2-Dec 2-Dec

Silver: 1 3 3 3 5 0

Gold: 0 0 1 0 0 1

1-Dec-12 2-Dec-12

Silver: 7 8

Gold: 1 1


=SUMPRODUCT(($B$3:$H$3=$B$6)*($A$4:$A$5=A7)*($B$4:$H$5))


Jai

 

[BharathBabu]

Got it.. Thank you very much