Lookup function/sum function

[Guest]

From what I can tell it seems to be working. I'm still testing it out and
it'll probably take me a few more hours to verify the data. I have to say
that this has been one very involved project they've given to me. I wish our
MRP system was more user friendly with this type of information.

As I was going through some of the data I noticed that the master release
quantity doesn't always equal all the later releases. The reason for this is
because the customer may only give us one release date and then will call us
later for the balance of the release dates. So what happens then is this
formula will pick up all the later releases and not the master release but
the amount won't be accurate because all the later releases don't equal the
master release quantity. Is there an easy way to add something to the formula
that will verify if all the later release quantities equal the master release
quantity? And if so then have it just calculate the way you set it up. And if
not then whatever the difference is then have it take that difference and
multiple it by the unit price so that I pick up any variance that is not yet
released?

I'm about ready to throw in the towel on this if I keep finding these types
of variances.

Thanks
SS

 

[Guest]

Sorry for the delayed response. I don't think I could do that without helper
columns.

In G1, enter
=B1&E1
and copy down

In H1, Enter
=IF((COUNTIF($G$1:$G$7,G1)>1)*(D1=0),SUMIF($G$1:$G$7,G1,$C$1:$C$7)/2=C1,TRUE)
and copy down

In cell I
=IF(H1,"",SUMPRODUCT(--($G$1:$G$7=G1),--($D$1:$D$7=0),$C$1:$C$7)-SUMPRODUCT(--($G$1:$G$7=G1),--($D$1:$D$7<>0),$C$1:$C$7))
and copy down

J1 = 11/06

In K1 (array entered w/Cntrl+Shift+Enter
=SUMPRODUCT(--(MONTH(A1:A7)=MONTH(J1)),--(YEAR(A1:A7)=YEAR(J1)),--(D17>=(--(COUNTIF(G1:G7,G1:G7)>1))),C1:C7)

In K2
=SUM(I1:I7)

In K3
=K1+K2


If helper cells are not an option, you will probably have to look at a VBA
solution.

 

[Guest]

No need to apologize for the delay. Let me digest all this code and piece it
together. I'll let you know how it goes. Thanks!

SS

 

[Guest]

Hello,
Everything is working great now! Thank you for all your help!

One last question and then I'm done with this project.

Say I wanted to track sales by individual day instead of by month. How would
I re-write the formula to sum up a specific date instead of by month/year?
The formula I am looking to change is the one you had me put in cell K1.

SS

 

[Guest]

For the formula I suggested in K2, it should probably be
=SUMPRODUCT(--(MONTH(A1:A7)=MONTH(J1)),--(YEAR(A1:A7)=YEAR(J1)),I1:I7)
so that it includes only those amounts for 11/06 (from the example we're
working with)

To modify it to look for a specific date:
K1==SUMPRODUCT(--(A1:A7=J1),--(D17>=(--(COUNTIF(G1:G7,G1:G7)>1))),C1:C7)

K2=SUMPRODUCT(--(A1:A7=J1),I1:I7)