I want to sum a range F1:F1000,if range A1:A1000 ="John" and date range B1:
B1000 =current financial year date(financial year is 1stApril to 31stMarch)
Name(colA)--------Date(ColB)---------------Amount(Col F)
John---------24-Jan-09--------------------------200
Mary--------30-Jan-09---------------------------100
Lucy--------02-Mar-09---------------------------180
John-------04-Apr-09-----------------------------300
Lucy------21-Apr-09------------------------------150
John-------23-May-09----------------------------500
Lucy------25-Jun-09-----------------------------100

I need funtion to sum John's Current year total Amout value,inthis case the
total is 800

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200907/1

Try this idea where your desired date are in d1 & d2
=sumproduct((b1:b11=d1)*(b1:b11<d2)*(a1:a11="john ")*f1:f11)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tkraju" <u16627@uwe wrote in message news:98996ed0eedbd@uwe...
I want to sum a range F1:F1000,if range A1:A1000 ="John" and date range B1:
B1000 =current financial year date(financial year is 1stApril to
31stMarch)
Name(colA)--------Date(ColB)---------------Amount(Col F)
John---------24-Jan-09--------------------------200
Mary--------30-Jan-09---------------------------100
Lucy--------02-Mar-09---------------------------180
John-------04-Apr-09-----------------------------300
Lucy------21-Apr-09------------------------------150
John-------23-May-09----------------------------500
Lucy------25-Jun-09-----------------------------100

I need funtion to sum John's Current year total Amout value,inthis case
the
total is 800

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200907/1

Hi,

If you are using 2007 then

=SUMIFS(F1:F1000,A1:A1000,"John",B1:B1000,"="&M1, B1:B1000,"<="&M2)

This illustrates how to reference the cell for the start and end of the
fiscal year. If you just reference the cell without the "-"& part you will
be testing two dates, no a range of dates onless you enter the date as text
entries:

=4/1/09
<=3/31/10


Otherwise in 2003 and earlier:

=SUMPRODUCT(--(A1:A1000="John"),--(B1:B1000=DATE(2009,4,1)),--(B1:B1000<=DATE(2010,3,31),F1:F1000)

This illustrates another method of referenceing the dates.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"tkraju" wrote:

I want to sum a range F1:F1000,if range A1:A1000 ="John" and date range B1:
B1000 =current financial year date(financial year is 1stApril to 31stMarch)
Name(colA)--------Date(ColB)---------------Amount(Col F)
John---------24-Jan-09--------------------------200
Mary--------30-Jan-09---------------------------100
Lucy--------02-Mar-09---------------------------180
John-------04-Apr-09-----------------------------300
Lucy------21-Apr-09------------------------------150
John-------23-May-09----------------------------500
Lucy------25-Jun-09-----------------------------100

I need funtion to sum John's Current year total Amout value,inthis case the
total is 800

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200907/1

thank you

Don Guillett wrote:
Try this idea where your desired date are in d1 & d2
=sumproduct((b1:b11=d1)*(b1:b11<d2)*(a1:a11="joh n")*f1:f11)

I want to sum a range F1:F1000,if range A1:A1000 ="John" and date range B1:
B1000 =current financial year date(financial year is 1stApril to
[quoted text clipped - 11 lines]
the
total is 800

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200907/1

thank you its working perfectly
Shane Devenshire wrote:
Hi,

If you are using 2007 then

=SUMIFS(F1:F1000,A1:A1000,"John",B1:B1000,"="&M1 ,B1:B1000,"<="&M2)

This illustrates how to reference the cell for the start and end of the
fiscal year. If you just reference the cell without the "-"& part you will
be testing two dates, no a range of dates onless you enter the date as text
entries:

=4/1/09
<=3/31/10

Otherwise in 2003 and earlier:

=SUMPRODUCT(--(A1:A1000="John"),--(B1:B1000=DATE(2009,4,1)),--(B1:B1000<=DATE(2010,3,31),F1:F1000)

This illustrates another method of referenceing the dates.
I want to sum a range F1:F1000,if range A1:A1000 ="John" and date range B1:
B1000 =current financial year date(financial year is 1stApril to 31stMarch)
[quoted text clipped - 9 lines]
I need funtion to sum John's Current year total Amout value,inthis case the
total is 800

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200907/1