Prev: Leave Blank If No Data
Next: cell reference as variable?
From: Emece on 25 Apr 2010 20:08
I have the following data:

Client January February March (and so on until December)
A 1 1 1 1 1 1 1 1
B 1 1 1 1 1 1

Each time I visit a client, I enter a 1 in the corresponding cell. I want to
detail, in a separate range, how many times I visit a client in each month.

Trying to clarify a bit more: I want to have a list in a cell with the
clients, a list in another cell with the months, and when I choose for
example A, February, to obtain a 3.

Is this possible?

Thanks in advance,

Regards,
Emece.-
From: Teethless mama on 25 Apr 2010 20:58
Assuming your data A1:M3
Criteria A10: A, B10: February

=LEN(SUBSTITUTE(INDEX(A1:M3,MATCH(A10,A1:A3,0),MATCH(B10,A1:M1,0))," ",))


"Emece" wrote:

> I have the following data:
>
> Client January February March (and so on until December)
> A 1 1 1 1 1 1 1 1
> B 1 1 1 1 1 1
>
> Each time I visit a client, I enter a 1 in the corresponding cell. I want to
> detail, in a separate range, how many times I visit a client in each month.
>
> Trying to clarify a bit more: I want to have a list in a cell with the
> clients, a list in another cell with the months, and when I choose for
> example A, February, to obtain a 3.
>
> Is this possible?
>
> Thanks in advance,
>
> Regards,
> Emece.-
From: Emece on 4 May 2010 11:26
Thank you very much for your help. I am sure it is the right answer altough
it didn't work in all cases. I think this is because I have merged cells in
the sheet, so in some cases I get the error #REF!

For example I have January merged from cells E1 to J1, and below six cells
where I enter a 1 for each visit to the client.

I don't want to bother you, but is there a possibility for me to send you
the worksheet so you can see it? I really need this to work properly.

Thanks in advance.

Regards,
Emece.-

"Teethless mama" wrote:

> Assuming your data A1:M3
> Criteria A10: A, B10: February
>
> =LEN(SUBSTITUTE(INDEX(A1:M3,MATCH(A10,A1:A3,0),MATCH(B10,A1:M1,0))," ",))
>
>
> "Emece" wrote:
>
> > I have the following data:
> >
> > Client January February March (and so on until December)
> > A 1 1 1 1 1 1 1 1
> > B 1 1 1 1 1 1
> >
> > Each time I visit a client, I enter a 1 in the corresponding cell. I want to
> > detail, in a separate range, how many times I visit a client in each month.
> >
> > Trying to clarify a bit more: I want to have a list in a cell with the
> > clients, a list in another cell with the months, and when I choose for
> > example A, February, to obtain a 3.
> >
> > Is this possible?
> >
> > Thanks in advance,
> >
> > Regards,
> > Emece.-
From: Emece on 4 May 2010 11:27
Thanks for your answer. I am sure that is the correct function, although it
doesn't work correctly in all cases. I think this is because I have merged
cells in the worksheet. I don't want to bother you too much, but can I
possibly send you the worksheet so you can check this? I really need this to
work properly. Thanks in advance.

Regards,
Emece.-


"Emece" wrote:

> I have the following data:
>
> Client January February March (and so on until December)
> A 1 1 1 1 1 1 1 1
> B 1 1 1 1 1 1
>
> Each time I visit a client, I enter a 1 in the corresponding cell. I want to
> detail, in a separate range, how many times I visit a client in each month.
>
> Trying to clarify a bit more: I want to have a list in a cell with the
> clients, a list in another cell with the months, and when I choose for
> example A, February, to obtain a 3.
>
> Is this possible?
>
> Thanks in advance,
>
> Regards,
> Emece.-
 | 
Pages: 1
Prev: Leave Blank If No Data
Next: cell reference as variable?