Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jdg jdg is offline
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jdg jdg is offline
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jdg jdg is offline
external usenet poster
 
Posts: 5
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining two ranges based on common a common value ahmed[_3_] Excel Discussion (Misc queries) 5 April 18th 09 05:04 PM
Combining spread sheets with common fields jjacksonn1966 Excel Discussion (Misc queries) 3 September 12th 08 04:27 PM
Add data based on a common field TracySLPS Excel Worksheet Functions 1 December 14th 07 05:04 PM
how can I convert data with fields in rows to fields as columns PiyushAg Excel Discussion (Misc queries) 3 July 2nd 07 05:46 AM
filtering common fields Ajit Munj Excel Discussion (Misc queries) 0 May 14th 07 01:58 PM


All times are GMT +1. The time now is 12:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"