Count of Dynamic Named Range with more criteria

Hi,

so the thing is, I have a table with many entries, which are always being updated. These are calls each saved with following criteria.

Each entry looks like:

Row A: Date, Row B: Name, Row C and D are not important, Row E: yes / no (If the call was succesfull or not)

I need to sum up / count for each separate date, how many calls an agent did (thats the Name in the table) and how many of those were succesfull.

The output is in a different workbook. I am thinking about copying it directly from the file, where it is updated, but this step is rather easy than the rest I am trying so for now I just copied the data into a new workbook.

What I did:

Dynamic Ranged Names:
Name: =OFFSET('Input Ret Suc'!$B$1;0;0;COUNTA('Input Ret Suc'!$B:$B);1)
Date: =OFFSET('Input Ret Suc'!$A$1;0;0;COUNT('Input Ret Suc'!$A:$A);1)
YesNo: =OFFSET('Input Ret Suc'!$E$1;0;0;COUNTA('Input Ret Suc'!$E:$E);1)

Ive managed to sum up how many times an agents name is at specific date (everytime a name is written, there was a call and so there is one entry).

This is counted by the following formula:

{=SUM((Date=N1)*(Name="ExampleName"))}

In this case I have written all the dates in a sepparate row (it will probably be a column later, but this shouldnt be an issue), so N1 is 01.08.2010.

ExampleName is just a name of an agent, that is mentioned in the table with all the data.

This seems to work.

The next step was, I tried get the amount of calls, that were succesfull simply by implementing another condition

{=SUM((Date=N1)*(Name="ExampleName")*(YesNo="yes"))}

This formula always returns with #N/A

When I try to Show the Calculation Steps, the first step is something like SUM({0;0;1;... and a long list of 0s and 1s but at the end, there is ... #N/A;#N/A}). After clicking evaluate, the next result is #N/A.

Just for info, maybe this information may help. At the moment, I have 2418 entries, in which the first column, so the first entry, is the title.

When I try to count the "yes" and "no" sepparately, simply by Countif, I did

=COUNTIF(YesNo;"yes") with the result 753
=COUNTIF(YesNo;"no") with the result 1663

Together this makes 2416 entries, considering the title, Im still missing one entry. Ive tried to autofilter the data sheet with all the entries but I only get yes and no as an option to choose in that row.

Can anyone help me with this issue?

Hi,

thanks for the help, the YesNo Range results in 2418 compared to the other two, which result in 2416.

Too bad the data is confidential, otherwise I would past it here. I cant seem to find the errornous cell or anything, but since there is data in every column Ive just replaced the Range for YesNo to the following:

=OFFSET('Input Ret Suc'!$E$1;0;0;COUNTA('Input Ret Suc'!$B:$B);1)

And it solved the problem. Thanks a lot for the help. It really helped a lot.