Struggling with formula
 

Hi

I have a spreadsheet withthe following data:

28/08/2008 09/05/2008 HINGE SA28553 8 £1.10 £0.77 £6.16
28/08/2008 09/05/2008 HINGE SA28566 8 £1.10 £0.77 £6.16
20/08/2008 28/08/2008 HINGE SA28389 24 £1.10 £0.77 £18.48
14/08/2008 21/08/2008 HINGE SA28268 12 £0.00 £0.00

What I need to see is the total number of units, of which XXX are Free
of Charge between xxxx date

Sorry, but I am struggling and got so far =COUNTIF(J2:J924,"£0.00")
but struggling with the rest, can anyone help

Ta

Struggling with formula
 

BabyPink,

In general:

=SUMPRODUCT((FirstDate<=Date"))*(LastDate=Date)*( Price1=0)*(Price2=0)*(Price3=0)*(Count))

FirstDate is the range with the starting dates
Date is a cell with the date of interest xxxx
LastDate is the range with the end dates
Price1,2, and 3 are the ranges with the prices - not sure is all must be zero
Count is the range with the number of units

All the multi-cell ranges should be once column, and the same number of rows ie, J2:J924, H2:H924,
G2:G924 etc.


You could add in another one for the product, too
=SUMPRODUCT((FirstDate<=Date"))*(LastDate=Date)*( Price1=0)*(Price2=0)*(Price3=0)*(Product="SA28389" )*(Count))

or have SA28389 in a cell...


HTH,
Bernie
MS Excel MVP


" wrote in message
...
Hi

I have a spreadsheet withthe following data:

28/08/2008 09/05/2008 HINGE SA28553 8 £1.10 £0.77 £6.16
28/08/2008 09/05/2008 HINGE SA28566 8 £1.10 £0.77 £6.16
20/08/2008 28/08/2008 HINGE SA28389 24 £1.10 £0.77 £18.48
14/08/2008 21/08/2008 HINGE SA28268 12 £0.00 £0.00

What I need to see is the total number of units, of which XXX are Free
of Charge between xxxx date

Sorry, but I am struggling and got so far =COUNTIF(J2:J924,"£0.00")
but struggling with the rest, can anyone help

Ta

Struggling with formula
 

On 2 Oct, 13:38, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
BabyPink,

In general:

=SUMPRODUCT((FirstDate<=Date"))*(LastDate=Date)*( Price1=0)*(Price2=0)*(Pri*ce3=0)*(Count))

FirstDate is the range with the starting dates
Date is a cell with the date of interest xxxx
LastDate is the range with the end dates
Price1,2, and 3 are the ranges with the prices - not sure is all must be zero
Count is the range with the number of units

All the multi-cell ranges should be once column, and the same number of rows ie, J2:J924, H2:H924,
G2:G924 etc.

You could add in another one for the product, too
=SUMPRODUCT((FirstDate<=Date"))*(LastDate=Date)*( Price1=0)*(Price2=0)*(Pri*ce3=0)*(Product="SA28389 ")*(Count))

or have SA28389 in a cell...

HTH,
Bernie
MS Excel MVP

" wrote in message

...
Hi

I have a spreadsheet withthe following data:

28/08/2008 09/05/2008 HINGE SA28553 8 £1.10 *£0.77 *£6.16
28/08/2008 09/05/2008 HINGE SA28566 8 £1.10 *£0.77 *£6.16
20/08/2008 28/08/2008 HINGE SA28389 24 £1.10 *£0.77 *£18.48
14/08/2008 21/08/2008 HINGE * *SA28268 12 £0.00 *£0.00

What I need to see is the total number of units, of which XXX are Free
of Charge between xxxx date

Sorry, but I am struggling and got so far =COUNTIF(J2:J924,"£0.00")
but struggling with the rest, can anyone help

Ta

Sorry sorry, I have not made it clear, I really do apologise I will
make it more simple

Order Date Description Qty Price
28/08/2008 HINGE SA28553 8 £1.10
28/08/2008 HINGE SA28566 8 £1.10
20/08/2008 HINGE SA28389 24 £1.10
04/08/2008 HINGE SA28268 12 £0.00

So I am looking for the result of "12" units were sold between 1
August 2008 and 28 August 2008

Struggling with formula
 

With your example table: dates in A, description in B, quantity in C, and price in D:

=SUMPRODUCT((A2:A8=A11)*(A2:A8<=A12)*(D2:D8=0)*C2 :C8)

with Aug 1 in A11 and Aug 28 in A12.

HTH,
Bernie
MS Excel MVP


" wrote in message
...
On 2 Oct, 13:38, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
BabyPink,

In general:

=SUMPRODUCT((FirstDate<=Date"))*(LastDate=Date)*( Price1=0)*(Price2=0)*(Pri*ce3=0)*(Count))

FirstDate is the range with the starting dates
Date is a cell with the date of interest xxxx
LastDate is the range with the end dates
Price1,2, and 3 are the ranges with the prices - not sure is all must be zero
Count is the range with the number of units

All the multi-cell ranges should be once column, and the same number of rows ie, J2:J924, H2:H924,
G2:G924 etc.

You could add in another one for the product, too
=SUMPRODUCT((FirstDate<=Date"))*(LastDate=Date)*( Price1=0)*(Price2=0)*(Pri*ce3=0)*(Product="SA28389 ")*(Count))

or have SA28389 in a cell...

HTH,
Bernie
MS Excel MVP

" wrote in message

...
Hi

I have a spreadsheet withthe following data:

28/08/2008 09/05/2008 HINGE SA28553 8 £1.10 £0.77 £6.16
28/08/2008 09/05/2008 HINGE SA28566 8 £1.10 £0.77 £6.16
20/08/2008 28/08/2008 HINGE SA28389 24 £1.10 £0.77 £18.48
14/08/2008 21/08/2008 HINGE SA28268 12 £0.00 £0.00

What I need to see is the total number of units, of which XXX are Free
of Charge between xxxx date

Sorry, but I am struggling and got so far =COUNTIF(J2:J924,"£0.00")
but struggling with the rest, can anyone help

Ta

Sorry sorry, I have not made it clear, I really do apologise I will
make it more simple

Order Date Description Qty Price
28/08/2008 HINGE SA28553 8 £1.10
28/08/2008 HINGE SA28566 8 £1.10
20/08/2008 HINGE SA28389 24 £1.10
04/08/2008 HINGE SA28268 12 £0.00

So I am looking for the result of "12" units were sold between 1
August 2008 and 28 August 2008