Search code examples
excelvbaexcel-formulaexcel-2019

What formula will allow me to complete certain SUMs depending on the data of a different cell?

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.

Solution

  • 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.