Best Way to Get Data Sums and Counts

[Alex20850]

I have the following fields (see below for data):
Date, Vendor, Main Category, Subcategory and Amount.
I want to create a summary page that will give me answers such as:
Sum Amount where Main Category="Contractor - Special Trades"
Sum Amount where Main Category='Contractor"
Sum Amount where Main Category="Cosmetologist"
I tried DSUM, but it got cumbersome with the column headings for each query.
There will be 10 or 15 queries to be updated when I make changes to the original data.

Is there an easier way?
Thanks.


[TABLE="width: 758"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Vendor[/TD]
[TD]Main Category[/TD]
[TD]Subcategory[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD="align: right"]02/10/15[/TD]
[TD]Summer Screen & Blinds[/TD]
[TD]Contractor - Special Trades[/TD]
[TD]Installation[/TD]
[TD="align: right"]$487.09[/TD]
[/TR]
[TR]
[TD="align: right"]07/17/15[/TD]
[TD]Tileco Masonry Ceramic Tile & Marble Co[/TD]
[TD]Contractor - Special Trades[/TD]
[TD]Masonry[/TD]
[TD="align: right"]$257.95[/TD]
[/TR]
[TR]
[TD="align: right"]11/29/15[/TD]
[TD]Asia Backflow Testing[/TD]
[TD]Contractor - Special Trades[/TD]
[TD]Other[/TD]
[TD="align: right"]-$69.68[/TD]
[/TR]
[TR]
[TD="align: right"]12/26/15[/TD]
[TD]Shelly at Clover[/TD]
[TD]Cosmetologist[/TD]
[TD]Hair Stylist/Hairdresser[/TD]
[TD="align: right"]-$36.18[/TD]
[/TR]
[TR]
[TD="align: right"]08/11/15[/TD]
[TD]Benjamin Cheng Chartered Accountant[/TD]
[TD]Financial Services[/TD]
[TD]Accountant/Auditor[/TD]
[TD="align: right"]$4.02[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/15[/TD]
[TD]Metropolitan Hotel[/TD]
[TD]Fitness Centre[/TD]
[TD]Fitness Centre - Class 2[/TD]
[TD="align: right"]$8.71[/TD]
[/TR]
[TR]
[TD="align: right"]10/28/15[/TD]
[TD]Broadway Dental[/TD]
[TD]Health Services[/TD]
[TD]Dentist[/TD]
[TD="align: right"]$361.80[/TD]
[/TR]
[TR]
[TD="align: right"]07/13/15[/TD]
[TD]Fasken Martineau DuMoulin LLP[/TD]
[TD]Office[/TD]
[TD]Barrister & Solicitor[/TD]
[TD="align: right"]-$27.47[/TD]
[/TR]
[TR]
[TD="align: right"]08/19/15[/TD]
[TD]McDonalds Restaurants[/TD]
[TD]Restaurant Class 1[/TD]
[TD]No Liquor Service[/TD]
[TD="align: right"]$488.43[/TD]
[/TR]
[TR]
[TD="align: right"]09/05/15[/TD]
[TD]New Sky Light Restaurant[/TD]
[TD]Restaurant Class 1[/TD]
[TD]No Liquor Service[/TD]
[TD="align: right"]$403.34[/TD]
[/TR]
[TR]
[TD="align: right"]03/26/15[/TD]
[TD]Homeglow Construction[/TD]
[TD]Contractor[/TD]
[TD]Alterations & Repairs[/TD]
[TD="align: right"]$542.70[/TD]
[/TR]
[TR]
[TD="align: right"]06/15/15[/TD]
[TD]Kens Home Renovation[/TD]
[TD]Contractor[/TD]
[TD]Alterations & Repairs[/TD]
[TD="align: right"]$577.54[/TD]
[/TR]
[TR]
[TD="align: right"]09/22/15[/TD]
[TD]Spring Financial[/TD]
[TD]Financial Services[/TD]
[TD]Finance Agent[/TD]
[TD="align: right"]$47.57[/TD]
[/TR]
</tbody>[/TABLE]

 

[LiveToExcel]

Hi Alex20850,

My recommendation would be to add a pivot table for your summary sheet. This will just make it so much easier, instead of worrying about a bunch of SUMIF or SUMIFS formulas.

Click in your data set (shown in your post above). Press Alt + n + v + Enter + Enter. Now you have a pivot table. (or go to Insert -> Pivot table -> add new sheet).

Move the "Main Category" to the Row Field (or Row Labels), then add the Amount field to the Values field. Now you have a nice summary of all of the Main Categories and how much was incurred. What's even better, if you have Excel 2010 or later, you can add Slicers that allow for very quick filtering of the data.

The possibilities are endless with the pivot table, and the best part is, you don't have to worry about the formulas.

I hope this helps.

 

[FDibbins]

While I agree that a PT will give you this quick and easy, you cannot always get the PT to display the way you want. Below is a formula-method to get what you want. Itr is based on your table above starting in A!, and on sheet4. I have4 shown how to use SUMIF and COUNTIF(1 criteria) and sumifS and countifS (multiple criteria)]
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Based on[/td][td]SUM[/td][td]COUNT[/td][td]SUM w/ Date[/td][td]Count w/ Date[/td][td]Start Sdate[/td][td]

2/20/2015​

[/td][/tr]

[tr][td]

2​

[/td][td]Contractor - Special Trades[/td][td]

$675.36​

[/td][td]

3​

[/td][td]

257.95​

[/td][td]

1​

[/td][td]End Date[/td][td]

8/11/2015​

[/td][/tr]

[tr][td]

3​

[/td][td]Cosmetologist[/td][td]

($36.18)​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Financial Services[/td][td]

$51.59​

[/td][td]

2​

[/td][td]

4.02​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Fitness Centre[/td][td]

$8.71​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Health Services[/td][td]

$361.80​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Office[/td][td]

($27.47)​

[/td][td]

1​

[/td][td]

-27.47​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Restaurant Class 1[/td][td]

$891.77​

[/td][td]

2​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Contractor[/td][td]

$1,120.24​

[/td][td]

2​

[/td][td]

1120.24​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]
[/table]
B2=SUMIF(Sheet4!$C$2:$C$14,A2,Sheet4!$E$2:$E$14)
C2=COUNTIF(Sheet4!$C$2:$C$14,A2)
D2=SUMIFS(Sheet4!$E$2:$E$14,Sheet4!$C$2:$C$14,Sheet5!$A2,Sheet4!$A$2:$A$14,">="&Sheet5!$G$1,Sheet4!$A$2:$A$14,"<="&Sheet5!$G$2)
E2=COUNTIFS(Sheet4!$C$2:$C$14,Sheet5!$A2,Sheet4!$A$2:$A$14,">="&Sheet5!$G$1,Sheet4!$A$2:$A$14,"<="&Sheet5!$G$2)
all copied down

 

[Alex20850]

Thanks, while I love Pivot Tables, for this particular project I wanted more control over simpler listing of the data than a Pivot Table. Once again, thanks.

Hi Alex20850,

My recommendation would be to add a pivot table for your summary sheet. This will just make it so much easier, instead of worrying about a bunch of SUMIF or SUMIFS formulas.

Click in your data set (shown in your post above). Press Alt + n + v + Enter + Enter. Now you have a pivot table. (or go to Insert -> Pivot table -> add new sheet).

Move the "Main Category" to the Row Field (or Row Labels), then add the Amount field to the Values field. Now you have a nice summary of all of the Main Categories and how much was incurred. What's even better, if you have Excel 2010 or later, you can add Slicers that allow for very quick filtering of the data.

The possibilities are endless with the pivot table, and the best part is, you don't have to worry about the formulas.

I hope this helps.