Need a formula for the SUM of a range of cells, but ONLY IF a different cell outside of that range has a date in it

Question

I've done some searching but could not find what I need, so I hope one of you kind souls can help.

I have a spreadsheet that someone set up for me a few years ago and I eventually set aside. Now I want to use it again and I am finding that I want to change some things, but I have very basic Excel skills.

I am tracking payments received for contract/gig work. I have more columns than this, but for simplicity's sake, I'll say I am working with columns for Fees, Bonuses, Date Paid, and then Total Rec'd.

When I schedule an assignment, the data goes across the rows. I enter the Company I'm doing work for, the date of the assignment, a name for it, and then the agreed-upon contract amounts for Fees and Bonuses. When I am paid, anywhere from 2 weeks to 3 months later, I enter the date in the Date Paid column.

What I need is for the total at the end of each row, under the Total Paid heading, to SUM the figures in that row from Fees and Bonuses ONLY IF there is a date in that row under the Date Paid heading.

If there's no date (meaning I have not gotten paid yet), I'd want the cells in the last column to be blank or zero. That would help me see who has paid me yet or not (the companies all have different pay cycles and the dollar amounts are not huge, so a missed payment can easily go unnoticed).

Basically, as an example:

  A           B        C            D              E
Company      Fee      Bonus       Date Paid      Total Rec'd  
 ABC Co      $20       $10       08/09/2022        $30  
 EFG Co.     $40       $5        08/10/2022        $45  
 XYZ Co.     $20       $5                           0  
 KLM Co.     $30       $12       08/12/2022        $42  

So, maybe a SUMIFS kind of thang? So, in my example above, IF a date is entered in a row under the column D heading, then in that row under Column E I want the SUM of the figures from the cells in columns B + C. But IF the Date Paid cell is blank, do not SUM, or leave it as zero, or blank.

Does this all make sense how I explained it? Oh, and by the way, in case it makes a difference, I'm using Open Office Calc.

Thanks in advance for any insight or help you can offer!

Answer 1

You can try SUMIFS() with date criteria so that it only sum those cells having dates to adjacent column. Use-

=SUMIFS(A:A,B:B,">" & DATE(1900,1,1))

Above formula will check every cell of B column and if it find any cell that have date greater than 01/01/1900, it will sum values from column A.

Answer 2

delete everything in your E column and use this in E2:

=ARRAYFORMULA(IF(A2:A="";;B2:B+C2:C))

additionally if you want E4 to be 0:

=ARRAYFORMULA(IF(A2:A="";;IF(D2:D=""; 0; B2:B+C2:C)))