software

I've got a counting problem which I hope someone can help me with.

This is my data:

Column headers:
Song Title,Singer,Duration,Venue 1,Venue 2,Venue 3, Venue n

Data:
Song A,John,1.45m,1,0,1
Song B,John,2.30m,0,1,1
Song C,Jane,2.43,1,0,0

The Venue columns show which songs were performed at each venue. There
are only 2 singers (John and Jane). The 1s and 0s indicate which songs
were performed at which Venue.

Here's my problem. For each venue column, I want to know:
1. How many songs John performed
2. How many songs Jane performed
3. The total duration of all the songs performed

I hope I've explained my problem clearly enough.
Thanks in advance for any help.
Ranj.--
ranj
------------------------------------------------------------------------
ranj's Profile: www.excelforum.com/member.php...oamp;userid=31098
View this thread: www.excelforum.com/showthread...hreadid=507664Hi

You could use something like this:
=SUMPRODUCT((B2:B1000=quot;Johnquot;)*(D21000))
This will give you the number of how many songs John has performed at Venue
1. By changing the D21000, you can determine which Venue to count.
To include the actual times, you can use:
=SUMPRODUCT((B2:B1000=quot;Johnquot;)*(D21000)*(C2:C1000 ))
You'll have to make sure that the 'time' is in Excel time format - and also
that the total cell is formatted the same way.

Hope this helps.
Andy.

quot;ranjquot; gt; wrote in message
...
gt;
gt; I've got a counting problem which I hope someone can help me with.
gt;
gt; This is my data:
gt;
gt; Column headers:
gt; Song Title,Singer,Duration,Venue 1,Venue 2,Venue 3, Venue n
gt;
gt; Data:
gt; Song A,John,1.45m,1,0,1
gt; Song B,John,2.30m,0,1,1
gt; Song C,Jane,2.43,1,0,0
gt;
gt; The Venue columns show which songs were performed at each venue. There
gt; are only 2 singers (John and Jane). The 1s and 0s indicate which songs
gt; were performed at which Venue.
gt;
gt; Here's my problem. For each venue column, I want to know:
gt; 1. How many songs John performed
gt; 2. How many songs Jane performed
gt; 3. The total duration of all the songs performed
gt;
gt; I hope I've explained my problem clearly enough.
gt; Thanks in advance for any help.
gt; Ranj.
gt;
gt;
gt; --
gt; ranj
gt; ------------------------------------------------------------------------
gt; ranj's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31098
gt; View this thread: www.excelforum.com/showthread...hreadid=507664
gt;

Assuming Song title column A
Name Column B
Duration ColumnC
Venue 1 ColumnD

The following can be place in column D and copied accross for other
venues
you may need to extend the range to greate than 3!

Johns songs count =SUMIF($B$1:$B$3,quot;Johnquot;,D$1$3)

Janes songs count =SUMIF($B$1:$B$3,quot;Janequot;,D$1$3)

Johns Duration =SUMPRODUCT(($B$1:$B$3=quot;Johnquot;)*(D$1$3)*($C$1:$C$ 3))

Janes Duration =SUMPRODUCT(($B$1:$B$3=quot;Janequot;)*(D$1$3)*($C$1:$C$ 3))

Total Duration =SUMPRODUCT($C$1:$C$3,D$1$3)

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=507664
Thanks for your help. Dav - your solution worked fine.
I guess it's easy when you know how!

Many thanks,
Ranj.--
ranj
------------------------------------------------------------------------
ranj's Profile: www.excelforum.com/member.php...oamp;userid=31098
View this thread: www.excelforum.com/showthread...hreadid=507664