I probably could have given you the answer with a bit more info from your
side. That being said you now understand sumproduct in a way that you never
would if I had just handed you the answer. On top of that you got an entire
evening's entertainment value out of it... ;-)
--
HTH...

Jim Thomlinson


"Studebaker" wrote:

Jim,

I printed out the website you referred me to and studied it last night and
this morning. I was making it too difficult and I got it to work!!
Thank you for referencing this website! Though I think for someone who might
not understand functions at all, this reference is a little over their head.

Here's the answer I came up with for those who want to know. It's probably
not written as efficiently as it could be and I do get a green triangle on
the cell where my formula is that says, "The formula in this cell refers to a
range that has additional #s adjacent to it". It's not an error, per se, b/c
it does give the answer...so I just told Excel to ignore the 'error'. I'm not
sure if it will pose a problem later.

=SUMPRODUCT(-(O3:O266<0)*(O3:O266))+SUMPRODUCT((I3:I266<"")*(O 3:O266="")*(I3:I266))

The (-) negative in the beginning is a negative sign not a single unary
operator the website talks about.

For those who want to know, it takes the $ amounts (the values) in column O
that are less than 0 (i.e. negative $ amounts aka the $ amounts of the
shortpaid invoices) and makes the shortpays positive. It then adds those $
values to the $ values of the invoices in column I that: a) exist in column I
and b) have a blank in column O--i.e. those invoices that haven't been paid
by the client yet.
The way the formula is set up it doesn't multiply $ amounts like you might
think it would giving you a huge, crazy answer.

I hope someone can understand what I said and hope it helps.

Thanks, again, Jim.

"Jim Thomlinson" wrote:

Try sumproduct instead. Check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Studebaker" wrote:

Hello,

I have 2 columns of invoices totals like below:

Column I Column O
Original Invoice Total Amount Shortpaid
$45,679.00 ($-255.00)
$123.00 $0.00
$1,500.00 blank
blank blank

I'm trying to sum column O to give a total of what the client still owes us.
I need to:

a) Sum column O if the amounts in column O are < 0 (which means the client
has shortpd the original invoice) AND
b) Sum column I (the amount of the original invoice) if the invoice has flat
out not been paid--so sum column I if column O is blank (not been pd) AND
column I has a number in it (if the invoice exists and there's no blank
space in column I)

I tried this but I get a "FALSE" in my cell:

=AND(SUMIF(O3:O265,"<0"),SUMIF(I3:I266,AND(ISNUMBE R(I3:I266), O3:O266="")))

Can someone help with this function (no macros please)?

Thanks very much!