Complex COUNTIFS

[ntanswell]

Hi,

I am using an Excel workbook to record data on input to our service, which comes from several different departments. I have one sheet with all of the raw data on it set up as a table with various columns (month processed, referring department, type of case etc) and various other sheets displaying the analysed information.

I have used COUNTIF and COUNTIFS to get information such as most frequently referring department, percentages of each case type etc. of the total workload from the raw data sheet but am struggling to produce a formula that will calculate how many cases we receive from each given department on a month by month basis. I have tried the following formula but it returns a #VALUE error:

=COUNTIFS(Table1[[#All],[Date processed]],">=1/4/2013",Table1[[#All],[Date processed]],"<=30/4/2013",Table1[[#Headers],[Department]],"Integrated transport")

Would it be possible to get a formula to calculate something of this complexity without re-formatting the data?

Hope someone can help?

Thanks.

[Kaper]

without test data (and having different date format here), I cannot check it, but it could be that
">=1/4/2013"
is a problem source.
I'd rather use ">=" & DATE(2013,4,1)
and the same at the end of month

[ntanswell]

It isn't the date string causing the problem as the formula works correctly when pulling through the total number of cases in each month - it only runs into difficulties when adding the 'per department' element into the equation.

[XOR LX]

Hi,

Clearly this is a case which is going to be very difficult to resolve without seeing an actual spreadsheet.

Are you able to upload one which illustrates your issue? Obviously replace any confidential/sensitive information with dummy data if necessary.

Regards