Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum \ Count
Hi,
I am trying to sum up a database which can be categorized under different months. The month is mentioned in on column A & the sum is in column B. I would like the formula to be in a diff sheet so that whenever I make changes in the main data, the same would get updated automatically. Can someone please help me with this. Regards, M A Rehman |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum \ Count
I have place an example at http://www.HelpExcel.com/examples
The name of the spreadsheet is db.xls -- http://HelpExcel.com "M A Rehman" wrote: Hi, I am trying to sum up a database which can be categorized under different months. The month is mentioned in on column A & the sum is in column B. I would like the formula to be in a diff sheet so that whenever I make changes in the main data, the same would get updated automatically. Can someone please help me with this. Regards, M A Rehman |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum \ Count
Thank you.. but I am still facing a problem.. the data where I have it
reflecting as a date... how do I change it to reflect as the month ? "galimi" wrote: I have place an example at http://www.HelpExcel.com/examples The name of the spreadsheet is db.xls -- http://HelpExcel.com "M A Rehman" wrote: Hi, I am trying to sum up a database which can be categorized under different months. The month is mentioned in on column A & the sum is in column B. I would like the formula to be in a diff sheet so that whenever I make changes in the main data, the same would get updated automatically. Can someone please help me with this. Regards, M A Rehman |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum \ Count
HI,
In column A, I have the date mentioned in the format DD/MM/YYYY. Now against these dates, I have certain amounts mentioned. I would like to put a formula in place where if I select a month as the criteria, the associated total for that month should be reflected alongside. In similar fashion, I would need to do a count & percentage as well. Is this possible ? "galimi" wrote: I have place an example at http://www.HelpExcel.com/examples The name of the spreadsheet is db.xls -- http://HelpExcel.com "M A Rehman" wrote: Hi, I am trying to sum up a database which can be categorized under different months. The month is mentioned in on column A & the sum is in column B. I would like the formula to be in a diff sheet so that whenever I make changes in the main data, the same would get updated automatically. Can someone please help me with this. Regards, M A Rehman |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum \ Count
=SUMPRODUCT(--(MONTH(Sheet1!A2:A20)=1),Sheet1!B2:B20)
or if the month is in a cell =SUMPRODUCT(--(MONTH(Sheet1!A2:A20)=A2),Sheet1!B2:B20) or if the dates could be multiple years, then put the 1st of the target month in A2 and use =SUMPRODUCT(--(Sheet1!A2:A20-DAY(Sheet1!A2:A20)+1=A2),Sheet1!B2:B20) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "M A Rehman" wrote in message ... Thank you.. but I am still facing a problem.. the data where I have it reflecting as a date... how do I change it to reflect as the month ? "galimi" wrote: I have place an example at http://www.HelpExcel.com/examples The name of the spreadsheet is db.xls -- http://HelpExcel.com "M A Rehman" wrote: Hi, I am trying to sum up a database which can be categorized under different months. The month is mentioned in on column A & the sum is in column B. I would like the formula to be in a diff sheet so that whenever I make changes in the main data, the same would get updated automatically. Can someone please help me with this. Regards, M A Rehman |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum \ Count
With real dates assumed in A2:A33, amounts in B2:B33
month numbers within D2:D13, viz: 1, 2, 3 ... 12 you could try something along these lines: In E2, normal ENTER: =SUMPRODUCT(--(MONTH($A$2:$A$33)=D2)) Returns the count of dates in col A for the month number in D2 In F2, normal ENTER: =SUMPRODUCT(--(MONTH($A$2:$A$33)=D2),$B$2:$B$33) Returns the sum of amounts in col B for the month number in D2 Select E2:F2, copy down to F13 to return for the 12 month numbers -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M A Rehman" wrote: HI, In column A, I have the date mentioned in the format DD/MM/YYYY. Now against these dates, I have certain amounts mentioned. I would like to put a formula in place where if I select a month as the criteria, the associated total for that month should be reflected alongside. In similar fashion, I would need to do a count & percentage as well. Is this possible ? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum \ Count
HI,
Can I send U the sheet ? Do you think you can help me with it this way ? Please let me know if its possible. Regards, M A Rehman "galimi" wrote: I have modified the spreadsheet to now reflect a date in column A (MM/DD/YYYY format), a random value in Column B and alteration on the formulaic array in column E to provide the solution I believe you are looking for. -- http://HelpExcel.com "M A Rehman" wrote: HI, In column A, I have the date mentioned in the format DD/MM/YYYY. Now against these dates, I have certain amounts mentioned. I would like to put a formula in place where if I select a month as the criteria, the associated total for that month should be reflected alongside. In similar fashion, I would need to do a count & percentage as well. Is this possible ? "galimi" wrote: I have place an example at http://www.HelpExcel.com/examples The name of the spreadsheet is db.xls -- http://HelpExcel.com "M A Rehman" wrote: Hi, I am trying to sum up a database which can be categorized under different months. The month is mentioned in on column A & the sum is in column B. I would like the formula to be in a diff sheet so that whenever I make changes in the main data, the same would get updated automatically. Can someone please help me with this. Regards, M A Rehman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing beginning minus sign from telephone numbers | Excel Discussion (Misc queries) | |||
Subtotals by count | Excel Discussion (Misc queries) | |||
Count consecutive dates only | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |