Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > microsoft.public.excel.programming > #108096 > unrolled thread
| Started by | rtour957 <rtour957.1061fc68@excelbanter.com> |
|---|---|
| First post | 2015-10-13 00:41 +0100 |
| Last post | 2015-10-13 23:26 -0400 |
| Articles | 2 — 2 participants |
Back to article view | Back to microsoft.public.excel.programming
Tracking unduplicated clients rtour957 <rtour957.1061fc68@excelbanter.com> - 2015-10-13 00:41 +0100
Re: Tracking unduplicated clients isabelle <i@v.invalid> - 2015-10-13 23:26 -0400
| From | rtour957 <rtour957.1061fc68@excelbanter.com> |
|---|---|
| Date | 2015-10-13 00:41 +0100 |
| Subject | Tracking unduplicated clients |
| Message-ID | <rtour957.1061fc68@excelbanter.com> |
Each clients has a unique number. Throughout a year they participate in different activities coded e.g. IS, CM, SW. They may participate in only one or all three of the activities. I need to come up with the annual report that would track unduplicated number of clients served in each of these activities. Any suggestions on how to do this would be highly appreciated. Thank you, Artur -- rtour957
[toc] | [next] | [standalone]
| From | isabelle <i@v.invalid> |
|---|---|
| Date | 2015-10-13 23:26 -0400 |
| Message-ID | <mvki24$28r$1@speranza.aioe.org> |
| In reply to | #108096 |
hi Artur,
eg for counting unduplicated number of clients of "CM"
it is an array formula to insert with CTRL+Shift+Enter
=COUNT(IF((B1:B100="CM")*(A1:A100>0)*(ROW(A1:A100)),1/FREQUENCY(MATCH(A1:A100&B1:B100,A1:A100&B1:B100,0),ROW(INDIRECT("1:"&ROWS(A1:A100))))))
isabelle
Le 2015-10-12 19:41, rtour957 a écrit :
> Each clients has a unique number. Throughout a year they participate in
> different activities coded e.g. IS, CM, SW. They may participate in only
> one or all three of the activities.
> I need to come up with the annual report that would track unduplicated
> number of clients served in each of these activities.
> Any suggestions on how to do this would be highly appreciated.
>
> Thank you, Artur
>
>
>
>
[toc] | [prev] | [standalone]
Back to top | Article view | microsoft.public.excel.programming
csiph-web