Hi

You could use something like this:
=SUMPRODUCT((B2:B1000="John")*(D2:D1000))
This will give you the number of how many songs John has performed at Venue
1. By changing the D2:D1000, you can determine which Venue to count.
To include the actual times, you can use:
=SUMPRODUCT((B2:B1000="John")*(D2:D1000)*(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.

"ranj" wrote in message
...

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:
http://www.excelforum.com/member.php...o&userid=31098
View this thread: http://www.excelforum.com/showthread...hreadid=507664