help with formula

[nicuolt]

DATA COD Marime Intrate Price Ramase
12/2/09 8167 44-46 10 57.68 5
21/1/09 8167 44-46 10 58.08 0
7/1/09 8167 44-46 10 54.85 10

Formula is
=SUMIFS(E:E,Stoc!B:B,Comenzi!D8,C:C,Comenzi!E8,F:F,">0",A:A,MIN(A:A)) but not
work. I want the next date or next price if F:F = 0.
If F:F=0, result 0.
needing a formulas

TKS

 

[~L]

This one makes more sense.

Where these conditions are true:
The formula is array-entered using CTRL+SHIFT+ENTER

The distance between eligible rows is never more than 4999 rows (if this is
not the case, change the 5000s to 10000s or more).

You meant the next date/price that meets all conditions implied by your
formula.

The references to Comenzi are meant to be absolute.

The COD column is all text and the comenzi reference is text OR the COD
column is all numbers and the comenzi reference is numbers

In cell G2:

The formula for date is
=IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDEX(A2:A5000,MATCH(1,(F2:$F5000<>0)*(B2:B5000=Comenzi!$D$8)*(C2:C5000=Comenzi!$E$8),0)),A2)

The formula for price is
=IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDEX(E2:E5000,MATCH(1,(F2:$F5000<>0)*(B2:B5000=Comenzi!$D$8)*(C2:C5000=Comenzi!$E$8),0)),E2)

 

[nicu]

Not work!
My formula is in the sheet comenzi. I want to calcul next price except date
with RAMASE 0 . In this case, rezult 58.08...
21/1/09 < 12/2/09, and in row (7/1/09 8167 44-46 10 54.85 0), RAMASE = 0

Help with this formula!
Tanks in avans!
Ex.

 

[nicu]

DATA COD Marime Intrate Price Ramase
12/2/09 8167 44-46 10 57.68 5
21/1/09 8167 44-46 10 58.08 10
7/1/09 8167 44-46 10 54.85 0
7/1/09 8044 42 7 51.21 7

IF search in tabel: Cod 8167, Marime 44-46, sum = 15 in RAMASE.
I want extract 10 (max with this price "58.08"). In formula result this
price: "58.08".
Other cod is for other search!
I'm sure is posible!

 

[~L]

Array enter:

=MAX(IF((Stoc!F:F<>0)*(Stoc!B:B=Comenzi!D8)*(Stoc!C:C=Comenzi!E8),Stoc!F:F))

 

[nicu]

Not work. Result = 0 with formula
=MAX(IF((Stoc!F:F<>0)*(Stoc!B:B=Comenzi!D8)*(Stoc!C:C=Comenzi!E8),Stoc!F:F))

Is posibble??: IF(AND("COD"=Comenzi!D8,"Marime"=Comenzi!E8,F:F>0),MIN(A:A
EXCEPT ROW( DATE with F = 0) and rezult is PRICE???... in this case 58.08
Or "Next" PRICE after NEXT date when F = 0??? with criteria Cod and Marime?

 

[~L]

I have tested the formulas before replying. The result of the formula with
the data you provided should be 10. It is 0 when the terms do not match or
when it is not array entered.

Does the formula: =Comenzi!D8=Stoc!B2 produce TRUE?
Does =comenzi!E8=Stoc!C2 produce TRUE?

Which do you prefer: the MAX price or the next price after the 0?

I think the formula you are describing now is
=INDEX(F:F,MATCH(MIN(IF((B:B=Comenzi!D8)*(C:C=Comenzi!E8)*(F:F>0),A:A)),A:A,0))
Which you must array-enter like before with control+shift+enter.

But that does not return the max price or the 'next' price, it returns the
price at the earliest date meeting all of those conditions.

 

[nicu]

You are the best!
thank you very very much!

I have tested the formulas before replying. The result of the formula with
the data you provided should be 10. It is 0 when the terms do not match or
when it is not array entered.

Does the formula: =Comenzi!D8=Stoc!B2 produce TRUE?
Does =comenzi!E8=Stoc!C2 produce TRUE?

Which do you prefer: the MAX price or the next price after the 0?

I think the formula you are describing now is:
=INDEX(F:F,MATCH(MIN(IF((B:B=Comenzi!D8)*(C:C=Comenzi!E8)*(F:F>0),A:A)),A:A,0))
Which you must array-enter like before with control+shift+enter.

But that does not return the max price or the 'next' price, it returns the
price at the earliest date meeting all of those conditions.