Conditional Sum in Array with wild card and between dates
 

Hi,

I am trying to SUM a column with two criteria between two dates. i.e. SUM
"Revenues" by "product" and by "customer" between two dates to create a
search function that enables me to search bewteen any two dates to determine
revenues by customer for any given period.

I ahve had some success with SUMPRODUCT but i am having difficulty in
getting excel to recognise the two dates - my current formula is not
recognising the start and end dates so if i exclude these from the formula
the amount is the same as it is when they are included i.e sum of revenues
since inception of the account.

I have also tried to use the conditional sum wizard - which does recognise
the dates - however, i have not been able to adapt the formula to be able to
include a wild card search for the customer.....Does anyone know how to use
the wizard to ascertain teh revenues over any given period????


i can send a copy of teh sheet if you need more info???

Conditional Sum in Array with wild card and between dates
 

Hi,

You are on the correct path, SUMPRODUCT

=SUMPRODUCT(--(A2:A10="Product1"),--(B2:B10="Customer1"),--(C2:C10=DATE(2009,1,1)),--(C2:C10<=DATE(2009,12,31)),D2:D10)

it is easier if you put the product, customer, start and end dates in cells
and refer to them:

=SUMPRODUCT(--(A2:A10=G1),--(B2:B10=F2),--(C2:C10=G3),--(C2:C10<=G4),D2:D10)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Hank01061567" wrote:

Hi,

I am trying to SUM a column with two criteria between two dates. i.e. SUM
"Revenues" by "product" and by "customer" between two dates to create a
search function that enables me to search bewteen any two dates to determine
revenues by customer for any given period.

I ahve had some success with SUMPRODUCT but i am having difficulty in
getting excel to recognise the two dates - my current formula is not
recognising the start and end dates so if i exclude these from the formula
the amount is the same as it is when they are included i.e sum of revenues
since inception of the account.

I have also tried to use the conditional sum wizard - which does recognise
the dates - however, i have not been able to adapt the formula to be able to
include a wild card search for the customer.....Does anyone know how to use
the wizard to ascertain teh revenues over any given period????


i can send a copy of teh sheet if you need more info???

Conditional Sum in Array with wild card and between dates
 

see your other post

--
Biff
Microsoft Excel MVP


"Hank01061567" wrote in message
...
Hi,

I am trying to SUM a column with two criteria between two dates. i.e. SUM
"Revenues" by "product" and by "customer" between two dates to create a
search function that enables me to search bewteen any two dates to
determine
revenues by customer for any given period.

I ahve had some success with SUMPRODUCT but i am having difficulty in
getting excel to recognise the two dates - my current formula is not
recognising the start and end dates so if i exclude these from the formula
the amount is the same as it is when they are included i.e sum of revenues
since inception of the account.

I have also tried to use the conditional sum wizard - which does recognise
the dates - however, i have not been able to adapt the formula to be able
to
include a wild card search for the customer.....Does anyone know how to
use
the wizard to ascertain teh revenues over any given period????


i can send a copy of teh sheet if you need more info???

Conditional Sum in Array with wild card and between dates
 

Yep this one works!

Thanks!

"Shane Devenshire" wrote:

Hi,

You are on the correct path, SUMPRODUCT

=SUMPRODUCT(--(A2:A10="Product1"),--(B2:B10="Customer1"),--(C2:C10=DATE(2009,1,1)),--(C2:C10<=DATE(2009,12,31)),D2:D10)

it is easier if you put the product, customer, start and end dates in cells
and refer to them:

=SUMPRODUCT(--(A2:A10=G1),--(B2:B10=F2),--(C2:C10=G3),--(C2:C10<=G4),D2:D10)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Hank01061567" wrote:

Hi,

I am trying to SUM a column with two criteria between two dates. i.e. SUM
"Revenues" by "product" and by "customer" between two dates to create a
search function that enables me to search bewteen any two dates to determine
revenues by customer for any given period.

I ahve had some success with SUMPRODUCT but i am having difficulty in
getting excel to recognise the two dates - my current formula is not
recognising the start and end dates so if i exclude these from the formula
the amount is the same as it is when they are included i.e sum of revenues
since inception of the account.

I have also tried to use the conditional sum wizard - which does recognise
the dates - however, i have not been able to adapt the formula to be able to
include a wild card search for the customer.....Does anyone know how to use
the wizard to ascertain teh revenues over any given period????


i can send a copy of teh sheet if you need more info???