Ask Your Question
0

How do I return a result based on blank cells?

asked 2021-01-12 18:58:51 +0100

Karmageddon gravatar image

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

edit retag flag offensive close merge delete

Comments

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.

Opaque gravatar imageOpaque ( 2021-01-12 19:44:49 +0100 )edit

Thanks for your comment. I only included BI because the formula builder asked for a third range. It is not important for my results.

Karmageddon gravatar imageKarmageddon ( 2021-01-12 19:47:40 +0100 )edit

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.

Opaque gravatar imageOpaque ( 2021-01-12 20:16:11 +0100 )edit

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.

Karmageddon gravatar imageKarmageddon ( 2021-01-13 05:48:42 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2021-01-12 20:05:44 +0100

Opaque gravatar image

updated 2021-01-12 23:35:07 +0100

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-01-12 18:58:51 +0100

Seen: 18 times

Last updated: Jan 12