Leave the cell blank if no data from the forumula

[Leannee]

I am making a spreadsheet to organise when rent is paid. Because the rent is paid on different days and figures in the month I created the formula to show the total they have paid each month.

=SUMPRODUCT((MONTH(D8:D250)=1)*(YEAR(D8:D250)=2018)*(E8:E250))


At the moment if nothing is written in the cells, e.g for July, this is showing as £0. I am trying to add to this formula so that the cell remains blank.

I have used the conditional formatting to make the writing clear however I want a running balance of what they owe so this isnt any good.

 

[Special-K99]

=IF(=SUMPRODUCT((MONTH(D8:D250)=1)*(YEAR(D8:D250)=2018)*(E8:E250))
=0,"",=SUMPRODUCT((MONTH(D8:D250)=1)*(YEAR(D8:D250)=2018)*(E8:E250))
)

You could shorten that to

=IF(SUMPRODUCT((TEXT(D8:D250,"yyyymm")="201801")*(E8:E250))=0,"",SUMPRODUCT((TEXT(D8:D250,"yyyymm"))="201801")*(E8:E250))

 

[Leannee]

This doesn't work. Its showing as 'Value'.

I even thought of changing the dates to mmyyyy but this shows as blank even when the price is there. Any ideas?

 

[Special-K99]

This doesn't work. Its showing as 'Value'.

I even thought of changing the dates to mmyyyy but this shows as blank even when the price is there. Any ideas?

You have text in D8:D250 or E8:E250.
For SUMPRODUCT to work the data in the ranges specified must all be numbers.

Try deleting any spaces that are in the ranges first.

 

[Special-K99]

Scratch that. I'm getting a VALUE error too.

Checking on it...

 

[Special-K99]

There's an error in the first formula (it had equal signs before the SUMPRODUCT) it should be

=IF(SUMPRODUCT((MONTH(D8:D250)=1)*(YEAR(D8:D250)=2018)*(E8:E250))=0,"",SUMPRODUCT((MONTH(D8:D250)=1)*(YEAR(D8:D250)=2018)*(E8:E250)))

second formula should be (brackets in wrong position)

=IF(SUMPRODUCT((TEXT(D8:D250,"yyyymm")="201801")*(E8:E250))=0,"",SUMPRODUCT((TEXT(D8:D250,"yyyymm")="201801")*(E8:E250)))

 

[Leannee]

Amazing! It's worked. Thanks for your help