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

R

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
 
D

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.
 
B

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
 
D

Domenic

Here's another way...

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

Hope this helps!
 
D

Dave Peterson

Please stop scaring me! <vbg>

Bernard said:
A small typoe (more coffee, Dave?). The formula in C2 should be
=if(countif($b$2:b2,b2)=1,b2,0)
best wishes
Click to expand...
 
R

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
 
D

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!
 
R

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
 
D

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!
 
R

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
 
D

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...
 
R

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
 
Top