Sum values in a range conditional upon dates and an additional fie
 

Hi, there, this question is kind of a follow up to the question linked below
which is in regard to summing values in a named range based upon date. I have
effectively 3 columns of data that interest me that are named as ranges:
Item_Name, Sale_Date and Sale_Amount. I am trying to create a spreadsheet
that is broken down by quarter so I am hoping for something that is basically

=SUMIF(Data_Range,(Item_Name=A$1 AND (1/31/08 -30 days)<Sale_Date<(1/31/08+2
months)),Sale_Amount)

I know that I can not write the formula above and that what I need is some
form of array but I am so behind on my excel skills that I have no idea how
to write the correct formula.

Any help would be really appreciated!


http://www.microsoft.com/office/comm...6-20ce5ca555ca

Sum values in a range conditional upon dates and an additionalfie
 

Try this....

http://www.mrexcel.com/archive/Dates/31867.html

I've got a module that works out which which FiscalYear, FiscalMonth
FiscalQuater, FiscalPeriod based on a transaction date which I've
pasted below.




Option Explicit
Const FMonthStart = 7 ' Numeric value representing the first
month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first
day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.
' -1 means the fiscal year starts in the
' previous calendar year.


Function GetFiscalYear(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
Function GetFiscalMonth(ByVal x As Variant)
' Function to obtain the month of the financial year of any given date

Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function

Function GetFiscalQuarter(ByVal x As Variant) As String

' =CONCATENATE(IF(MONTH(A1)<=3,"Quarter 3",IF(MONTH(A1)<=6,"Quarter
4", _
' IF(MONTH(A1)<=9,"Quarter 1","Quarter 2")))& " FY"&YEAR(A1))
'
Dim m
m = Month(x)

Select Case m
Case 1 To 3
GetFiscalQuarter = "Q3"
Case 4 To 6
GetFiscalQuarter = "Q4"
Case 7 To 9
GetFiscalQuarter = "Q1"
Case 10 To 12
GetFiscalQuarter = "Q2"
End Select
End Function
Function GetFiscalPeriod(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date
Dim FiscalYear As Integer

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalPeriod = Year(x) - 1 & "-" & Year(x)
Else
GetFiscalPeriod = Year(x) & "-" & Year(x) + 1
End If


End Function

Sum values in a range conditional upon dates and an additional
 

This is awesome and more than enough for date data manipulation, with the
correct formula how I would I write the array formula to add values from a
column if the data in column 1 is equal to my variable, while adding the date
range parameter based upon information in column two?

Sorry, I know this is a kind of annoying requests...

Jay

"Forgone" wrote:

Try this....

http://www.mrexcel.com/archive/Dates/31867.html

I've got a module that works out which which FiscalYear, FiscalMonth
FiscalQuater, FiscalPeriod based on a transaction date which I've
pasted below.




Option Explicit
Const FMonthStart = 7 ' Numeric value representing the first
month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first
day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.
' -1 means the fiscal year starts in the
' previous calendar year.


Function GetFiscalYear(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
Function GetFiscalMonth(ByVal x As Variant)
' Function to obtain the month of the financial year of any given date

Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function

Function GetFiscalQuarter(ByVal x As Variant) As String

' =CONCATENATE(IF(MONTH(A1)<=3,"Quarter 3",IF(MONTH(A1)<=6,"Quarter
4", _
' IF(MONTH(A1)<=9,"Quarter 1","Quarter 2")))& " FY"&YEAR(A1))
'
Dim m
m = Month(x)

Select Case m
Case 1 To 3
GetFiscalQuarter = "Q3"
Case 4 To 6
GetFiscalQuarter = "Q4"
Case 7 To 9
GetFiscalQuarter = "Q1"
Case 10 To 12
GetFiscalQuarter = "Q2"
End Select
End Function
Function GetFiscalPeriod(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date
Dim FiscalYear As Integer

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalPeriod = Year(x) - 1 & "-" & Year(x)
Else
GetFiscalPeriod = Year(x) & "-" & Year(x) + 1
End If


End Function

Sum values in a range conditional upon dates and an additional
 

Apologies, I think it may be more clear if I were to ask it as so:

Item Transaction Date Transaction Amount
chicken soup 1/31/2008 $112
chicken soup 2/7/2008 $144
chicken soup 7/7/2008 $65
Cheese 5/7/2008 $89
cheese 9/9/2008 $92

How would I write a formula that basically says please sum the transaction
amounts if the item is = chicken soup and the transaction date is greater
than or equal to1/1/08 and less than 4/1/08?

"Jay59874" wrote:

This is awesome and more than enough for date data manipulation, with the
correct formula how I would I write the array formula to add values from a
column if the data in column 1 is equal to my variable, while adding the date
range parameter based upon information in column two?

Sorry, I know this is a kind of annoying requests...

Jay

"Forgone" wrote:

Try this....

http://www.mrexcel.com/archive/Dates/31867.html

I've got a module that works out which which FiscalYear, FiscalMonth
FiscalQuater, FiscalPeriod based on a transaction date which I've
pasted below.




Option Explicit
Const FMonthStart = 7 ' Numeric value representing the first
month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first
day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.
' -1 means the fiscal year starts in the
' previous calendar year.


Function GetFiscalYear(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
Function GetFiscalMonth(ByVal x As Variant)
' Function to obtain the month of the financial year of any given date

Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function

Function GetFiscalQuarter(ByVal x As Variant) As String

' =CONCATENATE(IF(MONTH(A1)<=3,"Quarter 3",IF(MONTH(A1)<=6,"Quarter
4", _
' IF(MONTH(A1)<=9,"Quarter 1","Quarter 2")))& " FY"&YEAR(A1))
'
Dim m
m = Month(x)

Select Case m
Case 1 To 3
GetFiscalQuarter = "Q3"
Case 4 To 6
GetFiscalQuarter = "Q4"
Case 7 To 9
GetFiscalQuarter = "Q1"
Case 10 To 12
GetFiscalQuarter = "Q2"
End Select
End Function
Function GetFiscalPeriod(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date
Dim FiscalYear As Integer

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalPeriod = Year(x) - 1 & "-" & Year(x)
Else
GetFiscalPeriod = Year(x) & "-" & Year(x) + 1
End If


End Function

Sum values in a range conditional upon dates and an additional
 

Use cells to hold your criteria:

E1 = chicken soup
F1 = lower date boundary = 1/1/2008
G1 = upper date boundary = 3/31/2008

=SUMPRODUCT(--(A2:A6=E1),--(B2:B6=F1),--(B2:B6<=G1),C2:C6)

--
Biff
Microsoft Excel MVP


"Jay59874" wrote in message
...
Apologies, I think it may be more clear if I were to ask it as so:

Item Transaction Date Transaction Amount
chicken soup 1/31/2008 $112
chicken soup 2/7/2008 $144
chicken soup 7/7/2008 $65
Cheese 5/7/2008 $89
cheese 9/9/2008 $92

How would I write a formula that basically says please sum the transaction
amounts if the item is = chicken soup and the transaction date is greater
than or equal to1/1/08 and less than 4/1/08?

"Jay59874" wrote:

This is awesome and more than enough for date data manipulation, with the
correct formula how I would I write the array formula to add values from
a
column if the data in column 1 is equal to my variable, while adding the
date
range parameter based upon information in column two?

Sorry, I know this is a kind of annoying requests...

Jay

"Forgone" wrote:

Try this....

http://www.mrexcel.com/archive/Dates/31867.html

I've got a module that works out which which FiscalYear, FiscalMonth
FiscalQuater, FiscalPeriod based on a transaction date which I've
pasted below.




Option Explicit
Const FMonthStart = 7 ' Numeric value representing the first
month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first
day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.
' -1 means the fiscal year starts in the
' previous calendar year.


Function GetFiscalYear(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
Function GetFiscalMonth(ByVal x As Variant)
' Function to obtain the month of the financial year of any given date

Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function

Function GetFiscalQuarter(ByVal x As Variant) As String

' =CONCATENATE(IF(MONTH(A1)<=3,"Quarter 3",IF(MONTH(A1)<=6,"Quarter
4", _
' IF(MONTH(A1)<=9,"Quarter 1","Quarter 2")))& " FY"&YEAR(A1))
'
Dim m
m = Month(x)

Select Case m
Case 1 To 3
GetFiscalQuarter = "Q3"
Case 4 To 6
GetFiscalQuarter = "Q4"
Case 7 To 9
GetFiscalQuarter = "Q1"
Case 10 To 12
GetFiscalQuarter = "Q2"
End Select
End Function
Function GetFiscalPeriod(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date
Dim FiscalYear As Integer

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalPeriod = Year(x) - 1 & "-" & Year(x)
Else
GetFiscalPeriod = Year(x) & "-" & Year(x) + 1
End If


End Function

Sum values in a range conditional upon dates and an additional
 

Thank you so much, that is ingenious!!!

"T. Valko" wrote:

Use cells to hold your criteria:

E1 = chicken soup
F1 = lower date boundary = 1/1/2008
G1 = upper date boundary = 3/31/2008

=SUMPRODUCT(--(A2:A6=E1),--(B2:B6=F1),--(B2:B6<=G1),C2:C6)

--
Biff
Microsoft Excel MVP


"Jay59874" wrote in message
...
Apologies, I think it may be more clear if I were to ask it as so:

Item Transaction Date Transaction Amount
chicken soup 1/31/2008 $112
chicken soup 2/7/2008 $144
chicken soup 7/7/2008 $65
Cheese 5/7/2008 $89
cheese 9/9/2008 $92

How would I write a formula that basically says please sum the transaction
amounts if the item is = chicken soup and the transaction date is greater
than or equal to1/1/08 and less than 4/1/08?

"Jay59874" wrote:

This is awesome and more than enough for date data manipulation, with the
correct formula how I would I write the array formula to add values from
a
column if the data in column 1 is equal to my variable, while adding the
date
range parameter based upon information in column two?

Sorry, I know this is a kind of annoying requests...

Jay

"Forgone" wrote:

Try this....

http://www.mrexcel.com/archive/Dates/31867.html

I've got a module that works out which which FiscalYear, FiscalMonth
FiscalQuater, FiscalPeriod based on a transaction date which I've
pasted below.




Option Explicit
Const FMonthStart = 7 ' Numeric value representing the first
month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first
day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.
' -1 means the fiscal year starts in the
' previous calendar year.


Function GetFiscalYear(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
Function GetFiscalMonth(ByVal x As Variant)
' Function to obtain the month of the financial year of any given date

Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function

Function GetFiscalQuarter(ByVal x As Variant) As String

' =CONCATENATE(IF(MONTH(A1)<=3,"Quarter 3",IF(MONTH(A1)<=6,"Quarter
4", _
' IF(MONTH(A1)<=9,"Quarter 1","Quarter 2")))& " FY"&YEAR(A1))
'
Dim m
m = Month(x)

Select Case m
Case 1 To 3
GetFiscalQuarter = "Q3"
Case 4 To 6
GetFiscalQuarter = "Q4"
Case 7 To 9
GetFiscalQuarter = "Q1"
Case 10 To 12
GetFiscalQuarter = "Q2"
End Select
End Function
Function GetFiscalPeriod(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date
Dim FiscalYear As Integer

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalPeriod = Year(x) - 1 & "-" & Year(x)
Else
GetFiscalPeriod = Year(x) & "-" & Year(x) + 1
End If


End Function

Sum values in a range conditional upon dates and an additional
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jay59874" wrote in message
...
Thank you so much, that is ingenious!!!

"T. Valko" wrote:

Use cells to hold your criteria:

E1 = chicken soup
F1 = lower date boundary = 1/1/2008
G1 = upper date boundary = 3/31/2008

=SUMPRODUCT(--(A2:A6=E1),--(B2:B6=F1),--(B2:B6<=G1),C2:C6)

--
Biff
Microsoft Excel MVP


"Jay59874" wrote in message
...
Apologies, I think it may be more clear if I were to ask it as so:

Item Transaction Date Transaction Amount
chicken soup 1/31/2008 $112
chicken soup 2/7/2008 $144
chicken soup 7/7/2008 $65
Cheese 5/7/2008 $89
cheese 9/9/2008 $92

How would I write a formula that basically says please sum the
transaction
amounts if the item is = chicken soup and the transaction date is
greater
than or equal to1/1/08 and less than 4/1/08?

"Jay59874" wrote:

This is awesome and more than enough for date data manipulation, with
the
correct formula how I would I write the array formula to add values
from
a
column if the data in column 1 is equal to my variable, while adding
the
date
range parameter based upon information in column two?

Sorry, I know this is a kind of annoying requests...

Jay

"Forgone" wrote:

Try this....

http://www.mrexcel.com/archive/Dates/31867.html

I've got a module that works out which which FiscalYear, FiscalMonth
FiscalQuater, FiscalPeriod based on a transaction date which I've
pasted below.




Option Explicit
Const FMonthStart = 7 ' Numeric value representing the first
month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first
day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in
the
' current calendar year.
' -1 means the fiscal year starts in
the
' previous calendar year.


Function GetFiscalYear(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
Function GetFiscalMonth(ByVal x As Variant)
' Function to obtain the month of the financial year of any given
date

Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function

Function GetFiscalQuarter(ByVal x As Variant) As String

' =CONCATENATE(IF(MONTH(A1)<=3,"Quarter 3",IF(MONTH(A1)<=6,"Quarter
4", _
' IF(MONTH(A1)<=9,"Quarter 1","Quarter 2")))& " FY"&YEAR(A1))
'
Dim m
m = Month(x)

Select Case m
Case 1 To 3
GetFiscalQuarter = "Q3"
Case 4 To 6
GetFiscalQuarter = "Q4"
Case 7 To 9
GetFiscalQuarter = "Q1"
Case 10 To 12
GetFiscalQuarter = "Q2"
End Select
End Function
Function GetFiscalPeriod(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date
Dim FiscalYear As Integer

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalPeriod = Year(x) - 1 & "-" & Year(x)
Else
GetFiscalPeriod = Year(x) & "-" & Year(x) + 1
End If


End Function

Sum values in a range conditional upon dates and an additional
 

I am a little late here. Since I have spend sometimes working on this,
try this alternative formula.

=SUMPRODUCT(($A$2:$A$11="chicken
soup")*($B$2:$B$11=--"2008-01-01")*($B$2:$B$11<=--"2008-04-08")*$C$2:$C$11)

However, this formula hard code the criterias which is unlike Biff's
HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"Jay59874" wrote:

Apologies, I think it may be more clear if I were to ask it as so:

Item Transaction Date Transaction Amount
chicken soup 1/31/2008 $112
chicken soup 2/7/2008 $144
chicken soup 7/7/2008 $65
Cheese 5/7/2008 $89
cheese 9/9/2008 $92

How would I write a formula that basically says please sum the transaction
amounts if the item is = chicken soup and the transaction date is greater
than or equal to1/1/08 and less than 4/1/08?

"Jay59874" wrote:

This is awesome and more than enough for date data manipulation, with the
correct formula how I would I write the array formula to add values from a
column if the data in column 1 is equal to my variable, while adding the date
range parameter based upon information in column two?

Sorry, I know this is a kind of annoying requests...

Jay

"Forgone" wrote:

Try this....

http://www.mrexcel.com/archive/Dates/31867.html

I've got a module that works out which which FiscalYear, FiscalMonth
FiscalQuater, FiscalPeriod based on a transaction date which I've
pasted below.




Option Explicit
Const FMonthStart = 7 ' Numeric value representing the first
month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first
day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.
' -1 means the fiscal year starts in the
' previous calendar year.


Function GetFiscalYear(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
Function GetFiscalMonth(ByVal x As Variant)
' Function to obtain the month of the financial year of any given date

Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function

Function GetFiscalQuarter(ByVal x As Variant) As String

' =CONCATENATE(IF(MONTH(A1)<=3,"Quarter 3",IF(MONTH(A1)<=6,"Quarter
4", _
' IF(MONTH(A1)<=9,"Quarter 1","Quarter 2")))& " FY"&YEAR(A1))
'
Dim m
m = Month(x)

Select Case m
Case 1 To 3
GetFiscalQuarter = "Q3"
Case 4 To 6
GetFiscalQuarter = "Q4"
Case 7 To 9
GetFiscalQuarter = "Q1"
Case 10 To 12
GetFiscalQuarter = "Q2"
End Select
End Function
Function GetFiscalPeriod(ByVal x As Variant)
' Function to obtain the FiscalYear of any given date
Dim FiscalYear As Integer

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalPeriod = Year(x) - 1 & "-" & Year(x)
Else
GetFiscalPeriod = Year(x) & "-" & Year(x) + 1
End If


End Function