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.

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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: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

[ranj]

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

Many thanks,
Ranj.