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

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

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,"John",D$1:D$3)

Janes songs count =SUMIF($B$1:$B$3,"Jane",D$1:D$3)

Johns Duration =SUMPRODUCT(($B$1:$B$3="John")*(D$1:D$3)*($C$1:$C$ 3))

Janes Duration =SUMPRODUCT(($B$1:$B$3="Jane")*(D$1:D$3)*($C$1:$C$ 3))

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

Regards

Dav


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