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?

 

[Marcelo Branco]

In the VBA

But if you want/need to keep the time values in column G, see the last 2 posts for workarounds to accomodate the times in G.

Hi Jonmo

Agree 100%

M.

 

[O_Junior]

I think this is related to this thread
http://www.mrexcel.com/forum/excel-questions/815816-formula-error-2.html#post3985831

So, maybe this formula

=SUMPRODUCT(--($A$7:$A$1000="RL1"),--(INT($G$7:$G$1000)=TODAY()),--($J$7:$J$1000<>"OK"),$I$7:$I$1000)

M.

That's it Marcelo, thank you! But i really want to understand the formula. What is it doing exactly? Can someone explain it to me?

 

[Marcelo Branco]

The formula tests each condition producing arrays of logical values, True or False, for each one.

-- coerces (converts) those logical values to 1 or 0.

As G7:G1000 contains Date/Time, to do a comparison with TODAY() is necessary to extract the integer part - that is exactly what the INT function does in INT(G7:G1000)

To understand how Excel stores Date/Time see
Dates And Times In Excel

For an excellent explanation about SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

M.

 

[O_Junior]

The formula tests each condition producing arrays of logical values, True or False, for each one.

-- coerces (converts) those logical values to 1 or 0.

As G7:G1000 contains Date/Time, to do a comparison with TODAY() is necessary to extract the integer part - that is exactly what the INT function does in INT(G7:G1000)

To understand how Excel stores Date/Time see
Dates And Times In Excel

For an excellent explanation about SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

M.

All right, thank you again!

 

[Marcelo Branco]

All right, thank you again!

You are welcome. Thanks for the feedback.

M.