count data of multiple cells based on criteria

[ashish navale]

Hi..


I have a Tracker where I have to update the daily request processed under US or UK. In column A there is date(dd-mmm-yy), In column B there is request id(eg.1-12345678), In column C there is country data..i.e., UK or US. In the same workbook I have made a different sheet named Service request Count.

Now this sheet will show count of daily request processed...i.e count of column B as with reference to country..i.e. Uk/US. But I m unable to prepare the formula for it..there are two functions..."Sumproduct" and other "countif"...

Could you plz help me out which formula will be more useful...also could you show me how the formula will be? Plz help me out with this query..as its the big hurdle for me..ur help is appreciated...

 

[jeremymjp]

Don't have time to explain, but check out COUNTIFS, a COUNTIF with an S. Check out the help entry on it.

 

[ashish navale]

the COUNTIFS is not working as the column B is blank initially..as and when the column is filled with any id like 1-123456...it shud reflect the count..will sumproduct function help out?

 

[NARAYANK991]

Hi Ashish ,


This is the sort of report where a Pivot Table would be the easiest option.


When you are initially creating the Pivot Table , use an extended range with blank rows , which you will later fill with dates , service request IDs and country data.


When the Pivot Table is created , it will contain one entry which will be blank , which you can remove from the display by unchecking the relevant checkbox in the filter.


Narayan

 

[ashish navale]

Thanks Narayan for the valuable help...I suggested my manager about this...but the sheet in which I am going to create is filled with other data too..like daily volumes processed in the service request...and all the data is required in the same sheet itself..since the service request Id is the unique ones I need to get the count of it automatically when its filled...

I will explain u in more detail...

the sheet in which I m expected to get count has dates in drop down. Similar format of date is in main sheet where I will enter data... With reference to that date I m trying to get count of ids in UK/US. So Sumproduct function can help anyway?

 

[NARAYANK991]

Hi Ashish ,


Can you please confirm the following :


1. Your data is in the form of 3 columns - column A will contain dates , column B will contain the Service Request IDs , column C will contain the country codes which can be US or UK.


2. Your Service Request IDs will be unique.


3. On any date , you can have multiple Service Request IDs , from only US , or only UK or from both.


4. On a different worksheet , you will have a 2 or 3 column report - column A will contain dates , column B will contain the Count of Service Requests from the UK , column C will contain the Count of Service Requests from the US.


5. At any point in time , the Summary Report worksheet should reflect the up-to-date status of the data.


Narayan

 

[Hui]

=COUNTIFS(Date Range, Date ,Country Range, Country)


Date Range and Counry Range should be the same size

Counifs doesn't care if the ranges are blank

 

[NARAYANK991]

Hi Ashish ,


I have copied and pasted a part of the worksheet ; if yours is on the same lines , then the formulae for the count of service requests are according to what Hui has already posted :


=COUNTIFS($A$18:$A$46,"="&E18,$C$18:$C$46,"UK")


=COUNTIFS($A$18:$A$46,"="&E18,$C$18:$C$46,"US")


Date Service Request ID Country Date Service Requests from the UK Service Requests from the US

10/11/2011 1102 US 10/11/2011 0 2

10/11/2011 1103 US 10/12/2011 2 1

10/12/2011 1104 US 10/13/2011 0 0

10/12/2011 1105 UK 10/14/2011 0 0

10/12/2011 1106 UK 10/15/2011 1 0

10/15/2011 1107 UK 10/16/2011 0 1

10/16/2011 1108 US 10/17/2011 0 0


Narayan