Hi Anne. I'll explain in depth. I have 3 columns that pertain to this
formula: =AND(E3<D3,TODAY()G3). D3 is the number of items ordered.
E3 is the number of items received. G3 is the date that these items
are scheduled to come in. This formula works as it needs to , telling
me in red if the number of received items (E3) is less than the number
ordered (D3), ONLY if this happens past the scheduled date (G3), and
that scheduled date (G3) is past today's date. In addition to this
function, I also need this formula to allow for items that are
delivered or ordered at different times. For example, I need 4 pens,
but I received them in two different shipments, 3 in one shipment, and
1 in another shipment. I need to see this in excel so I know they came
in different shipments. But excel won't recognize 3+1 in the number
received column (E3) and work out the formula so that it does what my
original formula does. I need to rewrite the formula so that not only
does it do =AND(E3<D3,TODAY()G3), but that the cell has the ability to
recognize 3+1 if shipments come separately. BTW, these all happen in
individual cells, each row has something different ordered, so the
formula changes from E3 and D3 all the way thru E20 and D20. So it is
not about totalling at the bottom of the columns...Make sense. I hope
so. If anyone can let me know if this is possible...GREAT! Thanks.


--
Roz
------------------------------------------------------------------------
Roz's Profile: http://www.excelforum.com/member.php...o&userid=29746
View this thread: http://www.excelforum.com/showthread...hreadid=494894

Try this:
(first assuming that you have a column (C) containing the items you ordered,
like "pens")
In your formula, =AND(E3<D3,TODAY()G3)
Replace E3 with: (SUMIF(C3:C$20,C3,E3:E$20))
Replace D3 with: (SUMIF(C3:C$20,C3,D3:D$20))
Where 20 is the number of rows in your sheet.
This will allow your function to evaluate all of the ordered and recieved
pens beyond the specified date (whatever row you are in).

Probable bug:
If you ordered 20 pens, recieved 5, and then ordered 15 more, recieving all
15, then this function would still return red, even though you have 20 pens,
because it would have your number ordered calculated as 35 pens. Therefore,
you may need to keep D3 as is.

Sorry if this is a bit incoherent, I'm still not sure exactly what you need.
Try the function and tell me what you think.
--
Anne Murray


"Roz" wrote:


Hi Anne. I'll explain in depth. I have 3 columns that pertain to this
formula: =AND(E3<D3,TODAY()G3).