Create sum but ignore doulbe entries...need help!

[Rene]

Hi Experts,

I'm an Excel illiterate in need of help!

Here's what I need to do:

I have a list of people in my team who have conducted different events
for customers. The number of participants is listed behind each event
name. Also there is a data field in which the name of other team
members, which also participated is given. Since all member file their
events, a particular event may be listed twice, but that is visible due
to the field "other team members".

Now I want to query how many participants there were in total. But of
course I don't want to count events where two or more members were
present twice. How can I achieve this????

Example of file:

TeamMember Event Number of Participants Other Members present

Joe Event in Berlin 23 none
Bill Event in Rome 32 Joe
Joe Event in Rome 32 Bill
Mike Event in Paris 17 none


How can I count the total number of participants without counting the
Event in Rome twice?

Thanks for your help!
Rene

[Dave Peterson]

I would use a helper column and adjust the number of participants.

=if(countif($b$2:b2,b2)=1,c2,0)

Then copy it down the column.

Rene wrote:
>
> Hi Experts,
>
> I'm an Excel illiterate in need of help!
>
> Here's what I need to do:
>
> I have a list of people in my team who have conducted different events
> for customers. The number of participants is listed behind each event
> name. Also there is a data field in which the name of other team
> members, which also participated is given. Since all member file their
> events, a particular event may be listed twice, but that is visible due
> to the field "other team members".
>
> Now I want to query how many participants there were in total. But of
> course I don't want to count events where two or more members were
> present twice. How can I achieve this????
>
> Example of file:
>
> TeamMember Event Number of Participants Other Members present
>
> Joe Event in Berlin 23 none
> Bill Event in Rome 32 Joe
> Joe Event in Rome 32 Bill
> Mike Event in Paris 17 none
>
> How can I count the total number of participants without counting the
> Event in Rome twice?
>
> Thanks for your help!
> Rene

--

Dave Peterson

[Bernard Liengme]

A small typoe (more coffee, Dave?). The formula in C2 should be
=if(countif($b$2:b2,b2)=1,b2,0)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>I would use a helper column and adjust the number of participants.
>
> =if(countif($b$2:b2,b2)=1,c2,0)
>
> Then copy it down the column.
>
> Rene wrote:
>>
>> Hi Experts,
>>
>> I'm an Excel illiterate in need of help!
>>
>> Here's what I need to do:
>>
>> I have a list of people in my team who have conducted different events
>> for customers. The number of participants is listed behind each event
>> name. Also there is a data field in which the name of other team
>> members, which also participated is given. Since all member file their
>> events, a particular event may be listed twice, but that is visible due
>> to the field "other team members".
>>
>> Now I want to query how many participants there were in total. But of
>> course I don't want to count events where two or more members were
>> present twice. How can I achieve this????
>>
>> Example of file:
>>
>> TeamMember Event Number of Participants Other Members
>> present
>>
>> Joe Event in Berlin 23 none
>> Bill Event in Rome 32 Joe
>> Joe Event in Rome 32 Bill
>> Mike Event in Paris 17 none
>>
>> How can I count the total number of participants without counting the
>> Event in Rome twice?
>>
>> Thanks for your help!
>> Rene
>
> --
>
> Dave Peterson

[Bernard Liengme]

Sorry I misread the problem!!! More coffee for me!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>I would use a helper column and adjust the number of participants.
>
> =if(countif($b$2:b2,b2)=1,c2,0)
>
> Then copy it down the column.
>
> Rene wrote:
>>
>> Hi Experts,
>>
>> I'm an Excel illiterate in need of help!
>>
>> Here's what I need to do:
>>
>> I have a list of people in my team who have conducted different events
>> for customers. The number of participants is listed behind each event
>> name. Also there is a data field in which the name of other team
>> members, which also participated is given. Since all member file their
>> events, a particular event may be listed twice, but that is visible due
>> to the field "other team members".
>>
>> Now I want to query how many participants there were in total. But of
>> course I don't want to count events where two or more members were
>> present twice. How can I achieve this????
>>
>> Example of file:
>>
>> TeamMember Event Number of Participants Other Members
>> present
>>
>> Joe Event in Berlin 23 none
>> Bill Event in Rome 32 Joe
>> Joe Event in Rome 32 Bill
>> Mike Event in Paris 17 none
>>
>> How can I count the total number of participants without counting the
>> Event in Rome twice?
>>
>> Thanks for your help!
>> Rene
>
> --
>
> Dave Peterson

[Domenic]

Here's another way...

=SUMPRODUCT((B1:B4<>"")/COUNTIF(B1:B4,B1:B4&""),C1:C4)

Hope this helps!

In article <[email protected]>,
"Rene" <[email protected]> wrote:

> Hi Experts,
>
> I'm an Excel illiterate in need of help!
>
> Here's what I need to do:
>
> I have a list of people in my team who have conducted different events
> for customers. The number of participants is listed behind each event
> name. Also there is a data field in which the name of other team
> members, which also participated is given. Since all member file their
> events, a particular event may be listed twice, but that is visible due
> to the field "other team members".
>
> Now I want to query how many participants there were in total. But of
> course I don't want to count events where two or more members were
> present twice. How can I achieve this????
>
> Example of file:
>
> TeamMember Event Number of Participants Other Members present
>
> Joe Event in Berlin 23 none
> Bill Event in Rome 32 Joe
> Joe Event in Rome 32 Bill
> Mike Event in Paris 17 none
>
>
> How can I count the total number of participants without counting the
> Event in Rome twice?
>
> Thanks for your help!
> Rene

[Dave Peterson]

Please stop scaring me! <vbg>

Bernard Liengme wrote:
>
> A small typoe (more coffee, Dave?). The formula in C2 should be
> =if(countif($b$2:b2,b2)=1,b2,0)
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> >I would use a helper column and adjust the number of participants.
> >
> > =if(countif($b$2:b2,b2)=1,c2,0)
> >
> > Then copy it down the column.
> >
> > Rene wrote:
> >>
> >> Hi Experts,
> >>
> >> I'm an Excel illiterate in need of help!
> >>
> >> Here's what I need to do:
> >>
> >> I have a list of people in my team who have conducted different events
> >> for customers. The number of participants is listed behind each event
> >> name. Also there is a data field in which the name of other team
> >> members, which also participated is given. Since all member file their
> >> events, a particular event may be listed twice, but that is visible due
> >> to the field "other team members".
> >>
> >> Now I want to query how many participants there were in total. But of
> >> course I don't want to count events where two or more members were
> >> present twice. How can I achieve this????
> >>
> >> Example of file:
> >>
> >> TeamMember Event Number of Participants Other Members
> >> present
> >>
> >> Joe Event in Berlin 23 none
> >> Bill Event in Rome 32 Joe
> >> Joe Event in Rome 32 Bill
> >> Mike Event in Paris 17 none
> >>
> >> How can I count the total number of participants without counting the
> >> Event in Rome twice?
> >>
> >> Thanks for your help!
> >> Rene
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Rene]

Hi Domenic,

thanks for your help, however, I'm having a hard time understanding
this.... I've managed to get at least some sort of calculation going.
1) I've had to replace (B1:B4,B1:B4&"") by (B1:B4;B1:B4&"") to get
anything to work... is that correct?
2) For the example above I get a result of "105" participants, which
makes very little sense, since the simple sum only includes 104
participants... the correct answer should be 72.

Can you explain how your formula works?

Thanks for all your help!
Rene

[Domenic]

Using the example you provided, and assuming that A1:D4 contains the
data, the following formula...

=SUMPRODUCT((B1:B4<>"")/COUNTIF(B1:B4,B1:B4&""),C1:C4)

....breaks down as follows...

(B1:B4<>"") evaluates to:

{TRUE;TRUE;TRUE;TRUE}

COUNTIF(B1:B4,B1:B4&"") evaluates to:

{1;2;2;1}

C1:C4 evaluates to:

{23;32;32;17}

So this is what we get...

=SUMPRODUCT({TRUE;TRUE;TRUE;TRUE}/{1;2;2;1},{23;32;32;17})

The first array is divided by the second and gives us...

=SUMPRODUCT({1;0.5,0.5;1},{23;32;32;17})

*Note that the numerical equivalent of TRUE and FALSE is 1/0,
respectively.

Then, the first array here is multiplied by the second and gives us...

=SUMPRODUCT({23;16;16;17})

....which SUMPRODUCT sums and returns 72.

Hope this helps!

In article <[email protected]>,
"Rene" <[email protected]> wrote:

> Hi Domenic,
>
> thanks for your help, however, I'm having a hard time understanding
> this.... I've managed to get at least some sort of calculation going.
> 1) I've had to replace (B1:B4,B1:B4&"") by (B1:B4;B1:B4&"") to get
> anything to work... is that correct?
> 2) For the example above I get a result of "105" participants, which
> makes very little sense, since the simple sum only includes 104
> participants... the correct answer should be 72.
>
> Can you explain how your formula works?
>
> Thanks for all your help!
> Rene

[Rene]

Dear Domenic,

thanks for your elaborate answer! I think I got it to work! It ended up
being some problems with my German version of Excel.... I needed to
replace some specific terms.

However, since every answer brings about at least two questions... here
I go with at least one (I just answerd the second one myself... it even
works with three identical entries... I am overwelmed!).

Question: how can I include this in a "subtotal" function. My sheet
includes filters and I'd like the function only to relate to the
visible (unfiltered) data.

I really appreciate your help!

With the biggest respect for you Excel-experts,
Rene

[Domenic]

For a filtered list, try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,1)),M
ATCH(B2:B10,B2:B10,0)),ROW(B2:B10)-ROW(B2)+1),C2:C10))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the
ranges accordingly.

Hope this helps!

In article <[email protected]>,
"Rene" <[email protected]> wrote:

> Dear Domenic,
>
> thanks for your elaborate answer! I think I got it to work! It ended up
> being some problems with my German version of Excel.... I needed to
> replace some specific terms.
>
> However, since every answer brings about at least two questions... here
> I go with at least one (I just answerd the second one myself... it even
> works with three identical entries... I am overwelmed!).
>
> Question: how can I include this in a "subtotal" function. My sheet
> includes filters and I'd like the function only to relate to the
> visible (unfiltered) data.
>
> I really appreciate your help!
>
> With the biggest respect for you Excel-experts,
> Rene

[Rene]

Domenic,

thanks once again for your response! Unfortunately I get an error
message that the subtotal function

3,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,1

is invalid. (Of course I adjusted the formula in your posting to a
single line.

Any suggestions?

Thanks,
Rene

[Domenic]

Hmm... It probably has to do with your version of Excel. Would you
like me to email you a sample file? Maybe that may help...

In article <[email protected]>,
"Rene" <[email protected]> wrote:

> Domenic,
>
> thanks once again for your response! Unfortunately I get an error
> message that the subtotal function
>
> 3,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,1
>
> is invalid. (Of course I adjusted the formula in your posting to a
> single line.
>
> Any suggestions?
>
> Thanks,
> Rene

[Rene]

Hi Domenic,

that would be great! I'm using Excel 2003 (German - Multilanguage
version)... I had changed to English prior to testing your code.

Thanks,
Rene

[Domenic]

Sample file sent...

In article <[email protected]>,
"Rene" <[email protected]> wrote:

> Hi Domenic,
>
> that would be great! I'm using Excel 2003 (German - Multilanguage
> version)... I had changed to English prior to testing your code.
>
> Thanks,
> Rene