Search code examples
excelexcel-formulaexcel-2019

How can i use COUNTIFS only when there is data present in an adjacent cell?


I am trying to get G19 in sheet STATISTICS to only populate with data calculated from the COUNTIFS formula shown below when F19 of sheet STATISTICS has data within. If there is no data calculated for F19(i.e. an empty cell returned) (separate formula) then G19 should return a blank.

The COUNTIFS formula shown is counting the amount of L's within column O on sheet TRADE LOG between the dates of 01 Feb 2021 and 28 Feb 2021.

This is the formula I am currently using in G19 :

=IF(COUNTIFS('SHARES LOG'!O:O,"L",'SHARES LOG'!B:B,">="&DATE(2021,2,1),'SHARES LOG'!B:B,"<="&DATE(2021,2,28)), COUNTIFS('SHARES LOG'!O:O,"L",'SHARES LOG'!B:B,">="&DATE(2021,2,1),'SHARES LOG'!B:B,"<="&DATE(2021,2,28)),"")

If anyone could tweak this to make it produce the data as detailed above that would be great.


Solution

  • As F19 returns "" in the second part if the IF(...) then you can't test using ISBLANK(F19), so I would use the following (indented for clarity) for G19, basically wrap the original formula inside IF(TRIM(F19)="", "", ... original ... ):

      IF(TRIM(F19)="", "", 
        IF(COUNTIFS('SHARES LOG'!O:O,
                    "L",
                    'SHARES LOG'!B:B,
                    ">="&DATE(2021,2,1),
                    'SHARES LOG'!B:B,
                    "<="&DATE(2021,2,28)), 
           COUNTIFS('SHARES LOG'!O:O,
                    "L",
                    'SHARES LOG'!B:B,
                    ">="&DATE(2021,2,1),
                    'SHARES LOG'!B:B,
                    "<="&DATE(2021,2,28)), 
           "")
      )