Struggling with formula

  • Thread starter Thread starter babypink2807
  • Start date Start date
B

babypink2807

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
 
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


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
 
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


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
Click to expand...

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
 
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


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


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
Click to expand...

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
 
Back
Top