Comparing and sum'ing data

[danuk2006]

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?

[VBA Noob]

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

[Toppers]

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

[danuk2006]

TOP BANANNA !

cheers