How do i get certain text to populate in a cell depending on the content of another without using number hiding formatting?

[Excelnewman]

This is a financial worksheet calculating how many lost or won trades have been completed within a month period.

I am trying to get cell ```I5``` on sheet ```MONTHLY STATS - SPREADS``` to populate a ```0``` if there have been no ```L```'s to count between the dates of 1 Feb 21 and 28 Feb 21 on sheet ```SPREADS LOG```.

But I only want this ```0``` to populate if there is data present in cell ```H5```. If there is no data in ```H5```I would like ```I5``` to be blank. (note I cannot use a number format to hide these ```0```'s in cell ```I5``` as I am trying to average the complete ```I``` column )

Whilst it is doing this cell ```I5``` must also be able to run the ```COUNTIFS``` & ```SUM``` formulas shown below.

This is the current formula in cell ```I5``` :

```=IF(SUM(COUNTIFS('SPREADS LOG'!P: P,{"L"},'SPREADS LOG'!R:R,">="&DATE(2021,2,1),'SPREADS LOG'!R:R,"<="&DATE(2021,2,28)))=0,"",SUM(COUNTIFS('SPREADS LOG'!P: P,{"L"},'SPREADS LOG'!R:R,">="&DATE(2021,2,1),'SPREADS LOG'!R:R,"<="&DATE(2021,2,28))))```

If someone could tweak this formula, that would be great.

 

[vletm]

Excelnewman
Of course, You read Forum Rules before Your the1st posting or how?

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Please, reread those and
focus with How to get the Best Results at Chandoo.org-part.
Other than You would have some challenges to ... verify ... any of Your formulas or something
... without something ... ... a sample file.

 

[Peter Bartholomew]

It took a bit of reverse engineering to formulate the problem. I got to

= IF(
      SUM(
         COUNTIFS(
            Log[LostWon],{"L"},
            Log[Date],">="&DATE(2021,2,1),
            Log[Date],"<="&DATE(2021,2,28))
         )=0,
      "",
      SUM(
         COUNTIFS(
            Log[LostWon],{"L"},
            Log[Date],">="&DATE(2021,2,1),
            Log[Date],"<="&DATE(2021,2,28))
         )
      )

My observations would be that the SUM is superfluous unless {"L"} is a multi-term array constant. A further trick to avoid the need to recalculate the non-zero results would be to force an error by dividing into 1 twice and trapping the error with IFERROR. That would give

= IFERROR(
      1/(1/
         COUNTIFS(
            Log[LostWon],{"L"},
            Log[Date],">="&DATE(2021,2,1),
            Log[Date],"<="&DATE(2021,2,28))),
      "" )

For Excel 365, one could use

= LET(
      lost, COUNTIFS(
            Log[LostWon],"L",
            Log[Date],">="&DATE(2021,2,1),
            Log[Date],"<="&DATE(2021,2,28)),
      IF(lost>0, lost, "")
   )