Using AND and SUMIF function together

[Studebaker]

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(ISNUMBER(I3:I266), O3:O266="")))

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

Thanks very much!

 

[Jim Thomlinson]

Try sumproduct instead. Check out this link...

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

 

[Studebaker]

I saw a post from Peo Sjoblom from 2005 where he answered someone's question
about summing a column where the last row will be changing. He gave this
formula:
=SUM($A$1:INDEX(A:A,D1)) where A1 always the first cell and D1 holds the
last row number.

Can someone also tell me how to incorporate this INDEX function to your
function in order to sum my column O which will also have the final cell in
the column vary?

Thank you for your help!

 

[Studebaker]

Hello Jim,

I'm sorry, I read the web page you referred me to but for the most part it
got too complex for me. From what I could make out, I'm not sure SUMPRODUCT
could help (?).

I am just learning about functions and I know enough to try to create a
basic function but having trouble combining the SUMIF, AND, ISNUMBER and now
the INDEX functions.

If I could see the function then I can learn why/how certain combinations of
functions were used.

Thanks.

 

[Studebaker]

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<>"")*(O3: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]

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... ;-)