| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing and sum'ing data
Hi - I have 2 coloumns of mixed data, 1 with values and 1 with a catergory code. I would like to summarise the data, ie I may have 50 items of catergory "STK" but would like to know the total value. The aim is to have 2 sheets in excel, 1 as a summary page, the other as the data sheet. What formulae would I use please? -- danuk2006 ------------------------------------------------------------------------ danuk2006's Profile: http://www.excelforum.com/member.php...o&userid=36522 View this thread: http://www.excelforum.com/showthread...hreadid=562742 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing and sum'ing data
Try Data Filter advance filter on your list. Click Unique records. Copy these over to your summary page in cell A1 say. Put this forumla into cell B1 Note Change Data to Sheet name where your data is based. Change ranges as appropriate. =SUMPRODUCT(--(Data!$A$1:$A$100=Summary!A1)*(--(Data!B1:B100))) VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=562742 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing and sum'ing data
Try:
=Sumproduct(--(range1="STK"),(range2)) ranges cannot be columns i.e. must of form $A$1:$A$100 not $A:$A HTH "danuk2006" wrote: Hi - I have 2 coloumns of mixed data, 1 with values and 1 with a catergory code. I would like to summarise the data, ie I may have 50 items of catergory "STK" but would like to know the total value. The aim is to have 2 sheets in excel, 1 as a summary page, the other as the data sheet. What formulae would I use please? -- danuk2006 ------------------------------------------------------------------------ danuk2006's Profile: http://www.excelforum.com/member.php...o&userid=36522 View this thread: http://www.excelforum.com/showthread...hreadid=562742 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing and sum'ing data
TOP BANANNA ! cheers -- danuk2006 ------------------------------------------------------------------------ danuk2006's Profile: http://www.excelforum.com/member.php...o&userid=36522 View this thread: http://www.excelforum.com/showthread...hreadid=562742 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
