Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-09-2021, 03:47 AM
TomJ1 TomJ1 is offline Extracting Totals and Summary of raw data Windows 10 Extracting Totals and Summary of raw data Office 2010
Novice
Extracting Totals and Summary of raw data
 
Join Date: Nov 2021
Posts: 3
TomJ1 is on a distinguished road
Default Extracting Totals and Summary of raw data

Hi team,
I wonder if someone may be able to help.
I have a sheet with a list of events a person (email address) has attended and I'd like to extract/sumarize the data in columns either at the end or on a new sheet.

For each email calculate / display:


"Total number of all events booked". I do this today using =COUNTIF($D$2:$D$25140, D2)
"Total number of events booked by "Category of Event"
"Total number of Donations"
Total number of times (row(s)) this person has made a "Donation to Fund". For blank display 0.
"Sum of "Donation to Fund". If no amount state €0
Attached Images
File Type: png sample.png (22.6 KB, 25 views)
Reply With Quote
  #2  
Old 11-09-2021, 06:58 AM
ArviLaanemets ArviLaanemets is offline Extracting Totals and Summary of raw data Windows 8 Extracting Totals and Summary of raw data Office 2016
Expert
 
Join Date: May 2017
Posts: 656
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

Something like this?
Attached Files
File Type: xlsx EventRep.xlsx (21.4 KB, 5 views)
Reply With Quote
  #3  
Old 11-09-2021, 07:41 AM
TomJ1 TomJ1 is offline Extracting Totals and Summary of raw data Windows 10 Extracting Totals and Summary of raw data Office 2010
Novice
Extracting Totals and Summary of raw data
 
Join Date: Nov 2021
Posts: 3
TomJ1 is on a distinguished road
Default

Thanks Arvil.



Yes, but your suggestion is a bit beyond me I'm afraid!


T
Reply With Quote
  #4  
Old 11-09-2021, 08:20 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Extracting Totals and Summary of raw data Windows 7 64bit Extracting Totals and Summary of raw data Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,524
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Hi and welcome
You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

After doing that the answer to your requirement would be to use Excel's Filtering functionality

Perhaps post a sample sheet if needed, but be aware that merged cells are to be avoided at all costs


And as you can see, Arvi used a simple multi column table with a row for each event with which you can do what you want afterwards
Reply With Quote
  #5  
Old 11-09-2021, 08:35 AM
TomJ1 TomJ1 is offline Extracting Totals and Summary of raw data Windows 10 Extracting Totals and Summary of raw data Office 2010
Novice
Extracting Totals and Summary of raw data
 
Join Date: Nov 2021
Posts: 3
TomJ1 is on a distinguished road
Default

thanks Pecoflyer. good thing I capture the data in a 2 dimension sheet first! find attached a sample. But yes, you are right, having raw data is the first step.


T
Attached Files
File Type: xlsx sheet1.xlsx (8.9 KB, 1 views)
Reply With Quote
  #6  
Old 11-09-2021, 08:54 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Extracting Totals and Summary of raw data Windows 7 64bit Extracting Totals and Summary of raw data Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,524
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Still using XL2010?
Reply With Quote
  #7  
Old 11-09-2021, 09:04 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Extracting Totals and Summary of raw data Windows 7 64bit Extracting Totals and Summary of raw data Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,524
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Perhaps get started with a simple Pivot Table
There are nice tutorials at https://www.contextures.com/createpi...html#Tutorials
Attached Files
File Type: xlsx Copy of sheet1.xlsx (13.9 KB, 1 views)
Reply With Quote
  #8  
Old 11-10-2021, 12:38 AM
ArviLaanemets ArviLaanemets is offline Extracting Totals and Summary of raw data Windows 8 Extracting Totals and Summary of raw data Office 2016
Expert
 
Join Date: May 2017
Posts: 656
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

Quote:
Originally Posted by TomJ1 View Post
Yes, but your suggestion is a bit beyond me I'm afraid!
All tables are defined as Tables.
To define a Table, you select an area, or you activate any single cell in existing table, and then select Table from Insert Menu. You can then also specify a name for created Table (by default it will be Table#). When you activate any cell of existing Table, a Table Design Menu will be available with some options.
In Excel formulas, you can refer to components of Table (Header row, Table datarange, column datarange, an active cell in current row, etc., using special syntax.
When you add new row of data into Table, then usually all formats, data validations, formulas etc. are automatically expanded to new row(s).
When you edit Table name, or the name of any column in Table, all formulas in Workbook referring to this Table are adjusted accordingly;
When you refer to Table column, or to specific header, or to value in current row of Table, this reference doesn't depend on position - you can drag the column to another position in Table, and all formulas (except ones referring to certain column number) will work correctly.

To ensure that no typos are getting into DataEntry table, data validation lists are used whenever possible. The Tables on sheets Participiants, EventCategories, and Retreats are for registering such values, and are sources for those data validation lists. Excel recognizes as sources for data validation lists:
1. The list of values entered directly on creation;
2. The reference to cell range ON SAME SHEET;
3. A defined Name (Name Manager from Formulas Menu).
I used 3rd option and defined a column in appropriate registry Table as Named Range (lParticipiants, lEventCat, etc.) - this ensures those names will be dynamic (whenever a new row is added to registry Table, this option will be available in matching data validation list automatically).

Instead of SUMIF/COUNTIF, I prefer SUMIFS/COUNTIFS, as they allow any number of conditions. And as they work with single condition too, there is no reason to use both of them - so when in future there will be a need to add a 2nd condition to single one, you don't have to edit the rest of formula.

The rest is Excel as usual. You can replace Table syntax in formulas with regular range references, and all will work - until you add a new row into DataEntry table
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summary page of totals? srussell Mail Merge 1 08-13-2019 11:16 AM
Extracting data to another tab franz Excel 4 04-14-2018 06:05 PM
excel 2013 summary line totals float jassie Excel 7 03-04-2017 09:53 PM
Extracting Totals and Summary of raw data Extracting data, pivot tables, and maybe VBA help? rbexcelhelp Excel Programming 3 05-09-2015 12:13 AM
Extracting Totals and Summary of raw data Extracting data from excel Eric855 Word 6 07-25-2013 08:02 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:46 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft