Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Value of rows based on two common fields
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. -- JDG |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Value of rows based on two common fields
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" wrote in message ... 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. -- JDG |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Value of rows based on two common fields
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. -- JDG "Ashish Mathur" wrote: 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" wrote in message ... 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. -- JDG |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Value of rows based on two common fields
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 --------------- Jacob Skaria "jdg" wrote: 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. -- JDG |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Value of rows based on two common fields
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "jdg" wrote: 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. -- JDG "Ashish Mathur" wrote: 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" wrote in message ... 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. -- JDG |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Value of rows based on two common fields
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 -- JDG "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "jdg" wrote: 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. -- JDG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining two ranges based on common a common value | Excel Discussion (Misc queries) | |||
Combining spread sheets with common fields | Excel Discussion (Misc queries) | |||
Add data based on a common field | Excel Worksheet Functions | |||
how can I convert data with fields in rows to fields as columns | Excel Discussion (Misc queries) | |||
filtering common fields | Excel Discussion (Misc queries) |