Forum Discussion
Solved
Treating if(isblank) as a series
Hello, I have a spreadsheet to track my bills. When I pay a bill, I enter the date paid in the F column and the amount I owe is represented in the G column. When I enter any value into an F cell, the value in the corresponding G column is subtracted from my total owed at the bottom.
I have it setup as a huge If(isblank) statement but would like to streamline the equation to a line or two if possible.
Basically, I am looking for a function to sum the series =IF(ISBLANK(Fx),0,-(Gx)) from x = 3 to x = 10 where Fx and Gx refer to cell values.
Right now I am doing =SUM(G3:G10) + IF(ISBLANK(F3),0,-(G3)) +IF(ISBLANK(F4),0,-(G4)) ...... IF(ISBLANK(F10),0,-(G10))
It works but looks like a mess and would get even worse applied to larger data sets. Any help or ideas would be appreciated.
=SUM($G$3:$G$10)+SUMPRODUCT(N(IF(ISBLANK($F$3:$F$10),0,-$G$3:$G$10)))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
I can't but help thinking you are over-complicating the problem. An simpler alternative to subtracting the amounts from the overall total when dates are inserted, would be not to add them in the first place.
= SUMIFS(amount, date, "")- That is certainly the streamlined approach for my problem!
Both great responses, always good to know different approaches.
=SUM($G$3:$G$10)+SUMPRODUCT(N(IF(ISBLANK($F$3:$F$10),0,-$G$3:$G$10)))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- That seems to work but I think I need to spend some time understanding why haha. I am going to read up on the "sumproduct(n" part of that function because it is a bit foreign to me.
Thank you for the quick response!=SUM($G$3:$G$10)+SUMPRODUCT(IF(ISBLANK($F$3:$F$10),0,-$G$3:$G$10))
You are welcome.
Actually SUMPRODUCT(IF(ISBLANK works and "N(" within SUMPRODUCT(N(IF(ISBLANK isn't required. Therefore you don't have to spend time on the "N(" part.
