Sum Value of rows based on two common fields

[jdg]

Hi,

Trust I explain this easily and someone can help.

My spreadsheet has three columns; date, unique venue ID number and a value -
eg

Date Venue Number Score Overall Score
1/1/09 1234 10 ??
1/1/09 1234 8 ??
1/1/09 6789 9 ??
4/2/09 1234 6 ??

I need a forth column to calculate the sum value for each venue based on the
same date as venues can have multiple rows with different scores for the same
date.
I don't mind if each row has the sum value figure for the same venue on the
same date as I can de-dupe based on date/venue number.

Any assistance greatly appreciated.

 

[Ashish Mathur]

Hi,

The easiet way to sove this is to create a pivot table. Drag Data and Venue
number to the row area and score to the data area. Please the date and
venue in the row area depending upon whether you want to sum the score of
date by venue number or venue number by date

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[jdg]

thanx Ashish - have already done this. I need to be able to see the result
against the row of data though (for other reasons). Any thoughts on this one.
cheers.

 

[Jacob Skaria]

Try
Date in Col A
Venue number in ColB
Score in ColC
Row1 will have headers

In D2 enter below formula and copy down as required.

=SUMPRODUCT(--($A$2:$A$100=A2),--($B$2:$B$100=B2),$C$2:$C$100)

If this post helps click Yes

 

[Shane Devenshire]

Hi,

You may still be able to do the pivot table solution:

Add the Date, Vendor Number and Score to the row area and then add the Score
again to the Data area.

A second alternative is to use the Data, Subtotal command.

For functions you can use SUMIFS or SUMPRODUCT

=IF(AND(A2=A1,B2=B1),"",SUMPRODUCT(--($A$2:$A$5=A2),--($B$2:$B$5=B2),$C$2:$C$5))

This assumes the data is sorted on on columns A and B and that the first row
includes titles. So this formula goes into D2 and you copy it down.

 

[jdg]

Hi Jacob,

thanx this 'sort of' solves my problem.
As I add new rows of data the formulae does not automatically extend the
range beyond (as in your example) "$100" when I drag the cell formulae down
the other rows.
If I increase the row number beyound the number of rows of data the results
are not correct.
Is there a way to make this dynamic so the formulae recognises that
additional rows have been added?

Cheers