A counting question...

[ranj]

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.

 

[Guest]

Hi

You could use something like this:
=SUMPRODUCT((B2:B1000="John")*(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="John")*(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.

 

[Dav]

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$3)

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

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

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

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

Regards

Dav

 

[ranj]

Thanks for your help. Dav - your solution worked fine.
I guess it's easy when you know how!

Many thanks,
Ranj.