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

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

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

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

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

Sum \ Count
 

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

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 ?

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