Data collection Sum/Total
Here's how I understand your setup:
Column A has 365 dates (01.Jan - 31.Dec) (starting in A2) Column B has the weekday of the date in col.A. (starting in B2. col.header "Weekday") Column C has your "collected data" (col.header "amount") In order to get the sum of all Mondays / Tuesdays / ... I propose two approaches: 1 - Pivot table over columns B:C, pull the Days into "Row labels" and pull the amounts into "Values" (you must include the column headers in the definition of the data range) 2 - SUMIF: assume you want the result to be in rows D and E: enter the 7 weekday names (or values 1 - 7 depending on how you defined your weekdays in col.B) in col.D, starting at D2 in E2 enter this formula =SUMIF($A$2:$A$366,D2,$B$1:$B$366) and copy down to E8 "Rebecca" wrote: Hi Everyone, Hope someone has the answer to this little problem that I can't seem to sort. I am trying to extract figures from a table. that will allow me to select all numerical data entered on Mondays or Tuesdays ect I have a table 365 days long with a column for the date, a column for the day & a column for collected data. I wish to extract/collect the sum total of data for the different days of the week for a whole year. I have been trying to use the paste function/Lookup facility but cannot fine anything that gives the sum total of the required info. -- Thanks Becky |
Data collection Sum/Total (correction)
SUMIF should read:
2 - SUMIF: assume you want the result to be in columns D and E: ... (not rows) "Michael_R" wrote: Here's how I understand your setup: Column A has 365 dates (01.Jan - 31.Dec) (starting in A2) Column B has the weekday of the date in col.A. (starting in B2. col.header "Weekday") Column C has your "collected data" (col.header "amount") In order to get the sum of all Mondays / Tuesdays / ... I propose two approaches: 1 - Pivot table over columns B:C, pull the Days into "Row labels" and pull the amounts into "Values" (you must include the column headers in the definition of the data range) 2 - SUMIF: assume you want the result to be in rows D and E: enter the 7 weekday names (or values 1 - 7 depending on how you defined your weekdays in col.B) in col.D, starting at D2 in E2 enter this formula =SUMIF($A$2:$A$366,D2,$B$1:$B$366) and copy down to E8 "Rebecca" wrote: Hi Everyone, Hope someone has the answer to this little problem that I can't seem to sort. I am trying to extract figures from a table. that will allow me to select all numerical data entered on Mondays or Tuesdays ect I have a table 365 days long with a column for the date, a column for the day & a column for collected data. I wish to extract/collect the sum total of data for the different days of the week for a whole year. I have been trying to use the paste function/Lookup facility but cannot fine anything that gives the sum total of the required info. -- Thanks Becky |
All times are GMT +1. The time now is 03:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com