I'm using a sumif formula which is working to calculate a sum of data on a
sheet but it is calculating the sum for the entire sheet which goes back five
years. I only want the sum for the previous 6 months which will automatically
update itself with the answer. the formula I have is
=SUMIF(Type,"P28R",Logbook!O:O)
where "type" is a column heading and P28R is a criteria under the type
column and logbook is the name of the entire sheet in excel.

Thanks for any help!!!

You want to use a SUMPRODUCT function

http://www.kan.org/tips/excel_sumproduct_advanced1.php

Your formula would be something like this:

=SUMPRODUCT(--(time(today()-180)),--(Type="P28R"),Logbook!O:O)

whe

Time = the range of cells with your dates
Type = the range of cells with all of your classifications

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"tiger_27" wrote:

I'm using a sumif formula which is working to calculate a sum of data on a
sheet but it is calculating the sum for the entire sheet which goes back five
years. I only want the sum for the previous 6 months which will automatically
update itself with the answer. the formula I have is
=SUMIF(Type,"P28R",Logbook!O:O)
where "type" is a column heading and P28R is a criteria under the type
column and logbook is the name of the entire sheet in excel.

Thanks for any help!!!

There is a fundamental flaw with this formula. Unless you are using Excel
2007, you CANNOT use whole clumns in SUMPRODUCT, it must be explicit ranges.

Also, you should specify what constitutes 6 months, 183 days ago, anything
in the months prior to say February as of today, anything prior to 21st
January prior to today. Assuming anything rpior to 21st Jan, use

=SUMPRODUCT(--(LogBook!A2:A200=DATE(YEAR(TODAY()),MONTH(TODAY() )-6,DAY(TODAY()))),
--(Logbook!B2:B200="P28R"),Logbook!O2:O200)


--
__________________________________
HTH

Bob

"M Kan" <tipsoftheweek at gmail dot com wrote in message
...
You want to use a SUMPRODUCT function

http://www.kan.org/tips/excel_sumproduct_advanced1.php

Your formula would be something like this:

=SUMPRODUCT(--(time(today()-180)),--(Type="P28R"),Logbook!O:O)

whe

Time = the range of cells with your dates
Type = the range of cells with all of your classifications

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"tiger_27" wrote:

I'm using a sumif formula which is working to calculate a sum of data on
a
sheet but it is calculating the sum for the entire sheet which goes back
five
years. I only want the sum for the previous 6 months which will
automatically
update itself with the answer. the formula I have is
=SUMIF(Type,"P28R",Logbook!O:O)
where "type" is a column heading and P28R is a criteria under the type
column and logbook is the name of the entire sheet in excel.

Thanks for any help!!!