I would like it to go through and display all values in a column once with the totals for that category, is this even possible in excel?

[whereskris]

Hi I have data laid out like below,
I am trying to write a formula to go through 1000's of rows in column B (Category) then display all of the records once and add up the totals
e.g. CCON $123.04
COTH $456.06

I have been able to do this with =SUMIFS(U27:U10000,F27:F10000,"="&I11,I27:I10000,"<="&J7,I27:I10000,">="&J6)
But I haven't been able to do it dynamically I have to actually type CCON in a cell for it to search for matches, I would like it to go through and display all values in a column once with the totals for that category, is this even possible?

Even better if possible then to display the above but between a start and end date entered in separate cells lets say A1 and A2

[TABLE="width: 1016"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]



Project ID[/TD]
[TD]



Category[/TD]
[TD]




Resource Category Description[/TD]
[TD]




Acctg Date[/TD]
[TD]



Description[/TD]
[TD]



Journal ID[/TD]
[TD]



Vendor Name[/TD]
[TD]




Transaction Amount $[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]CCON[/TD]
[TD]Consultants[/TD]
[TD="align: right"]07/31/2014[/TD]
[TD]PMO Alloc June 2014[/TD]
[TD]WG140703[/TD]
[TD]n/a[/TD]
[TD]4,721[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]CCON[/TD]
[TD]Consultants[/TD]
[TD="align: right"]05/09/2014[/TD]
[TD]PMO Alloc Apr 2014[/TD]
[TD]LM0358[/TD]
[TD]n/a[/TD]
[TD]6,407[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]CCON[/TD]
[TD]Consultants[/TD]
[TD="align: right"]06/19/2014[/TD]
[TD]PMO Alloc May 2014[/TD]
[TD]WG20140613[/TD]
[TD]n/a[/TD]
[TD]154[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]CCON[/TD]
[TD]Consultants[/TD]
[TD="align: right"]05/09/2014[/TD]
[TD]PMO Alloc Apr 2014[/TD]
[TD]LM0358[/TD]
[TD]n/a[/TD]
[TD]203[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]CCON[/TD]
[TD]Consultants[/TD]
[TD="align: right"]04/01/2014[/TD]
[TD]Tfr 474001 Mar 2014[/TD]
[TD]LM0336[/TD]
[TD]n/a[/TD]
[TD]107[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]11/18/2014[/TD]
[TD]As per Quotation 7AU-703000710[/TD]
[TD]AP00248634[/TD]
[TD]GE Energy Measurement & Control Pty Ltd[/TD]
[TD]38,770[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]04/01/2014[/TD]
[TD]Under contract 002-2103. Contr[/TD]
[TD]AP00237600[/TD]
[TD]GE Energy Measurement & Control Pty Ltd[/TD]
[TD]463,536[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]06/10/2014[/TD]
[TD]Progress payment #2 upon Submi[/TD]
[TD]AP00240681[/TD]
[TD]GE Energy Measurement & Control Pty Ltd[/TD]
[TD]463,536[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]06/24/2014[/TD]
[TD]Progress payment #3 Upon Procu[/TD]
[TD]AP00241322[/TD]
[TD]GE Energy Measurement & Control Pty Ltd[/TD]
[TD]231,768[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]10/08/2014[/TD]
[TD]Progress payment #5 Upon Mobil[/TD]
[TD]AP00246648[/TD]
[TD]GE Energy Measurement & Control Pty Ltd[/TD]
[TD]77,256[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]09/26/2014[/TD]
[TD]Accrue Unmatched PO Receipts[/TD]
[TD]PO00246123[/TD]
[TD]n/a[/TD]
[TD]77,256[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]10/01/2014[/TD]
[TD]Accrue Unmatched PO Receipts[/TD]
[TD]PO00246124[/TD]
[TD]n/a[/TD]
[TD](77,256)[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]11/07/2014[/TD]
[TD]Progress payment #4 Upon Shipm[/TD]
[TD]AP00248166[/TD]
[TD]GE Energy Measurement & Control Pty Ltd[/TD]
[TD]154,512[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]10/31/2014[/TD]
[TD]Accrue Unmatched PO Receipts[/TD]
[TD]PO00247694[/TD]
[TD]n/a[/TD]
[TD]154,512[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]10/31/2014[/TD]
[TD]WIP SysAssets Internallyfunded[/TD]
[TD]0000247865[/TD]
[TD]n/a[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]10/31/2014[/TD]
[TD]WIP SysAssets Internallyfunded[/TD]
[TD]0000247866[/TD]
[TD]n/a[/TD]
[TD]154,512[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]11/01/2014[/TD]
[TD]Accrue Unmatched PO Receipts[/TD]
[TD]PO00247695[/TD]
[TD]n/a[/TD]
[TD](154,512)[/TD]
[/TR]
[TR]
[TD]683234[/TD]
[TD]COTH[/TD]
[TD]Contractors - Other[/TD]
[TD="align: right"]11/01/2014[/TD]
[TD]WIP SysAssets Internallyfunded[/TD]
[TD]0000247865[/TD]
[TD]n/a[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Try Pivot Table.

 

[whereskris]

Thanks Gaz chops, but I would like to dump the data in one tab, changes from project to project and do it by formula to populate a dashboard.
I haven't had anything to do with pivot tables so probably should learn them and see if it can accomplish what I need, I am thinking it will make it necessat to add another tab to my sheet that's all.
ie. p6_dump, p6_pivot_table, managers_report amonst many others