Suming and Subtracting

[O_Junior]

Hey guys,

I need some help. I need a formula that, sum from I7:I1000 if the cells A7:A1000 are filled with "RL1", and G7:G1000 are filled with TODAY() date, but i need to subtract the value of I7:I1000 if the cells J7:J1000 are filled with "OK". I don't know how to do that and I've been trying to come up with a solution for weeks now! Can someone help me?

 

[Scott Huish]

I don't think you need to subtract anything, you just need to not include it in the sum since it's the same range:

If you are on XL2007 or higher:
=SUMIFS(I7:I1000,A7:A1000,"RL1",G7:G1000,TODAY(),J7:J1000,"<>OK")

 

[bbott]

Try:

=SUMIFS(I7:I1000,A7:A1000,"RL1",G7:G1000,"="&TODAY())-SUMIF(J7:J1000,"OK",I7:I1000)

This will subtract the OKs regardless of whether or not column A = "RL1" and column G = today. Is that what you want?

 

[Jonmo1]

To include the TODAY() criteria on column G

=SUMIFS(I7:I1000,A7:A1000,"RL1",G7:G1000,TODAY(),J7:J1000,"<>OK")

 

[West Man]

but i need to subtract the value of I7:I1000 if the cells J7:J1000 are filled with "OK"

Subtract the sum? Subtract from what?

 

[O_Junior]

Subtract the sum? Subtract from what?

like I've said, i need to subtract the value of I7:I1000 IF the cells J7:J1000 are filled with "OK".

 

[Scott Huish]

But if you don't include it at all there is no need to subtract, so only sum if J7:J1000 is NOT OK would accomplish the same thing.

=SUMIFS(I7:I1000,A7:A1000,"RL1",G7:G1000,TODAY(),J7:J1000,"<>OK")

 

[O_Junior]

But if you don't include it at all there is no need to subtract, so only sum if J7:J1000 is NOT OK would accomplish the same thing.

=SUMIFS(I7:I1000,A7:A1000,"RL1",G7:G1000,TODAY(),J7:J1000,"<>OK")

I need to sum the column I7:I1000, because its the column that gives me the information about how many hours of work i have in that particular machine (RL1), and once a production order is finished, i'm going to put an OK in the column J7:J1000 so that hours is not counted anymore.
I hope i did myself clear. Sorry for the bad English

 

[Jonmo1]

and once a production order is finished, i'm going to put an OK in the column J7:J1000 so that hours is not counted anymore.

That's what the formula does.
If there is "OK" in J7:J1000, then the value in I7:I1000 is not counted in the sum.

 

[O_Junior]

It is giving me the formula result Volatile and only a zero in the cell!