1

enter image description hereThis 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.

Insider
  • 95
  • 12
  • better to show how your data is structured on the `SPREADS LOG` sheet and clarify further on what exactly "Whilst it is doing this cell I6 must also be able to run the COUNTIFS & SUM formulas shown below." is aiming to achieve... – Terry W Mar 31 '21 at 00:24

1 Answers1

2

Here is a formula that will do what you're intending.

Cell H6:

=LET(LOSSES,COUNTIFS('SPREADS LOG'!$P:$P,"L",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<="&EDATE($A6,1)),WINS,COUNTIFS('SPREADS LOG'!$P:$P,"W",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1)),IF(AND(LOSSES=0,WINS=0),"",WINS))

Cell I6:

=LET(LOSSES,COUNTIFS('SPREADS LOG'!$P:$P,"L",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<="&EDATE($A6,1)),WINS,COUNTIFS('SPREADS LOG'!$P:$P,"W",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1)),IF(AND(LOSSES=0,WINS=0),"",LOSSES))

Using an empty string "" instead of 0 works perfectly for excluding the cell from SUM and COUNTIFS formulas without breaking them.

Please note that you cannot use the other cell's value for deciding whether to populate a 0 value or not, since this would create a circular reference.

Versions without LET:

Cell H6:

=IF(AND(COUNTIFS('SPREADS LOG'!$P:$P,"L",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1))=0,COUNTIFS('SPREADS LOG'!$P:$P,"W",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1))=0),"",COUNTIFS('SPREADS LOG'!$P:$P,"W",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1)))

Cell I6:

=IF(AND(COUNTIFS('SPREADS LOG'!$P:$P,"L",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1))=0,COUNTIFS('SPREADS LOG'!$P:$P,"W",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1))=0),"",COUNTIFS('SPREADS LOG'!$P:$P,"L",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1)))

These formulas assume the dates in your column A contain date values (e.g. 01.03.2021 for March) and are formatted with a custom formatting. This allows you to simply copy the formula for the entire column without manually having to change the dates. I would strongly recommend this approach as it has no disadvantages to your current approach, but a potentially tremendous upside when you add it to your repertoire of automation. If you decide to stick to your approach, you will need to change the conditions inside the COUNTIFS.

Edited because I misunderstood the question and provided an incorrect answer.

riskypenguin
  • 2,139
  • 1
  • 10
  • 22
  • Hi @riskypenguin, thanks very much for your help, I really appreciate it. I have inputted the formulas however it gives the NAME error. I have tried to see why and cannot work it out. I am not too bothered about manually changing dates as most of my sheets are done this way. Column A of the sheet we are working on does not contain any dates, simply text as you can see on screen. Hope this helps? – Insider Mar 30 '21 at 14:04
  • The `NAME` error is most likely due to your version of Excel not yet supporting [LET](https://support.microsoft.com/en-gb/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999?ui=en-US&rs=en-GB&ad=GB) yet. I added a version without `LET`. Please note that there is no visible difference between entering the months as text and entering them as dates and using a custom formatting like `MMMM` (this is applicable for my Excel version, might differ for yours). I would strongly recommend using dates here instead of text. – riskypenguin Mar 30 '21 at 14:13
  • Thanks again. Can I ask which version of Excel you are using, I would like to upgrade. Formulas both inputted without errors however I am unsure they are producing the result we are looking for. So far in March, there has been one ```L``` which is recorded in the ```SPREADS LOG``` sheet, and it has not shown in the 'lost' column of the sheet we are working on. If it makes things easier I can email the sheet to you, I understand how hard it can be working only with a screenshot. – Insider Mar 30 '21 at 14:31
  • Sure, feel free to send it to riskypenguin_so@protonmail.com and I'll take a look later today. – riskypenguin Mar 30 '21 at 14:39
  • Ah, I forgot: I'm using Microsoft Office 365 under an educational license provided by my university. – riskypenguin Mar 30 '21 at 15:59