I’m trying to add my accounts receivable by using a query; summing only the invoice values (column AT) where the paid date (BG) is blank. Column BI is the payment amount. I’ve tried several formulas and can’t come up with the right one. A few examples of what I’ve tried. Thanks for reading.
=SUMPRODUCT(AT:AT,ISBLANK(BG:BG)) ------ this returns the sum of the whole column. best result so far
=SUMIF(BG:BG,ISBLANK(BG:BG),BI:BI) ------- result is “0”
=DSUM(AT:BH,AT:AT,ISBLANK(BG:BG)) ------ Err504
What is the difference of invoice values (column AT) and Column BI is the payment amount. Can’t recognize why three columns should play a role here.
Thanks for your comment. I only included BI because the formula builder asked for a third range. It is not important for my results.
It is not important for my results.
but for SUMIF() formula the third argument is the “Sum range”; i.e the range of values to be summed up fulfilling the condition given by the other arguments.
Column AT is the total of each invoice, and BI is the client payment - almost always the full amount but occasionally paid in instalments.
I discovered something important this evening… the cells I’m querying are not empty. Column BI does contain an If/Then formula that renders the cell blank until the invoice paid date is filled. I typed numbers into a couple cells and they were summed, no problem using the SUMPRODUCT function.
Back to the drawing board on this one.
Hello,
use
=SUMIF(BG:BG;"";AT:AT) or
=SUMPRODUCT(ISBLANK(BG:BG);AT:AT)
If both formulas result in 0 assure that the cells in column BG are really blank. Also keep in mind that function ISBLANK() treats a formula as non-blank content of a cell, even if the formula evaluates to "" (see LibeOffice Help - ISBLANK)
Hope that helps.