0

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.

JohnnieL
  • 1,192
  • 1
  • 9
  • 15
Insider
  • 95
  • 12
  • `This is the formula I am currently using in G16` - should that be `g19`? – JohnnieL Feb 08 '21 at 08:57
  • Just edited, yes it should be – Insider Feb 08 '21 at 10:17
  • One other question `If there is no data calculated for F19` how is "no data" defined? blank/zero/"no data"? – JohnnieL Feb 08 '21 at 10:24
  • Defined as a blank column. ```=IF(COUNTIFS('SHARES LOG'!O:O,"W",'SHARES LOG'!B:B,">="&DATE(2021,2,1),'SHARES LOG'!B:B,"<="&DATE(2021,2,28)), COUNTIFS('SHARES LOG'!O:O,"W",'SHARES LOG'!B:B,">="&DATE(2021,2,1),'SHARES LOG'!B:B,"<="&DATE(2021,2,28)),"")``` That is the current formula in ```F19``` – Insider Feb 08 '21 at 10:30

1 Answers1

1

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)), 
       "")
  )
JohnnieL
  • 1,192
  • 1
  • 9
  • 15
  • Thanks for your help, I have wrapped the formula as suggested however now getting the #NAME? error when there IS data entered into F19 (note G19 should now be conducting the COUNTIF when there is data in F19) – Insider Feb 08 '21 at 10:54
  • 1
    can you paste exactly what you have typed in to the cell please? `#name?` is because a function name can not be resolved, in this case it can only be `TRIM` - is your local language english? if not you may need to use the local equivalent of trim. if it is english then there may be a typo – JohnnieL Feb 08 '21 at 10:58
  • I used : ```=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)),””))``` and yes my local language is english – Insider Feb 08 '21 at 10:59
  • 1
    I have pasted that into my test sheet and i get no error.remove the trim and see if that makes any difference. Can you confirm your language also? thanks – JohnnieL Feb 08 '21 at 11:02
  • Removing ```TRIM``` and using the following : ```=IF(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)),””))```still gives the ```#NAME?``` error. Language is english – Insider Feb 08 '21 at 11:06
  • 1
    That cant be in the cell because there is a syntax error here: `=IF(F19)=""` - you are missing an open bracket. Nonetheless, bizarre: a couple of things: 1. what is the value of `F19`? if that is `#name?` that error will be passed on to `g19` 2. change the end of the function from `””` to `""` – JohnnieL Feb 08 '21 at 11:10
  • Fixed. I changed ”” to "" and added a 0 between the latter. Thanks again. – Insider Feb 08 '21 at 11:14