RE: sumif with two conditions,current financial year date

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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.aspx/excel-functions/200907/1

.

Relevant Pages

  • RE: Comparing 2 sets of data to fill in gaps
    ... "Shane Devenshire" wrote: ... active cell. ... You will need to adjust this reference according to your data. ... highlighted cell after step 2, then the reference would be A3. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Da Duck - 29 Sept 2010
    ... Incomplete reference. ... "Sutton also illustrates and writes a bi-weekly cartoon in TV Guide, ... to the op-ed pages of The New York Times and to Rolling Stone, Time, ...
    (rec.arts.comics.strips)
  • Re: Make a copy of structure within of a HoH?
    ... > Kindly consider this simplified script which illustrates my question ... reference, rather than assigning a scalar to a hash: ...
    (comp.lang.perl.misc)
  • Re: Merge Cells, but keep backgrounds
    ... In addition you can have the text box reference a cell where you can enter ... To create a reference in the textbox to a cell select the border of the text ... "Shane Devenshire" wrote: ...
    (microsoft.public.excel)
  • Re: Da Duck - 29 Sept 2010
    ... Incomplete reference. ... "Sutton also illustrates and writes a bi-weekly cartoon in TV Guide, ... to the op-ed pages of The New York Times and to Rolling Stone, Time, ...
    (rec.arts.comics.strips)