This is an example spreadsheet of trading wins and losses within a year.
I am trying to get cell I6
on sheet MONTHLY STATS - SPREADS
to populate a 0
if there have been no L
's to count between the dates of 1 Mar 21
and 31 Mar 21
on sheet SPREADS LOG
.
But I only want this 0
to populate if there is data present in cell H6
. If there is no data in H6
I would like I6
to be blank. (note I cannot use a number format to hide these 0
's in cell I6
as I am trying to average the complete I
columnm later on in the sheet)
Whilst it is doing this cell I6
must also be able to run the COUNTIFS
& SUM
formulas shown below.
IMPORTANT NOTE: This must also work the reverse way. e.g. if there are no W
's between the dates I want a 0
populated in the WON
column once data is put into the LOST
column.
This is the formula I am currently using in cell I6
:
=IF(H6="", "",IF(COUNTIFS('SPREADS LOG'!P:P,"L",'SPREADS LOG'!R:R,">="&DATE(2021,3,1),'SPREADS LOG'!R:R,"<="&DATE(2021,3,31)),COUNTIFS('SPREADS LOG'!P:P,"L",'SPREADS LOG'!R:R,">="&DATE(2021,3,1),'SPREADS LOG'!R:R,"<="&DATE(2021,3,31)),"0"))
If anyone could tweak this or suggest an edit that would be great.