1

I am trying to substitute '!!!!!!' for a formula to count the number of W in column P on sheet TRADE LOG between the dates of 01 Feb 2021 and 28 Feb 2021 in hardcoded format.

This is the current formula :

=IF(COUNTIFS('TRADE LOG'!P:P,"W",'TRADE LOG'!B:B,">="&DATE(2021,2,1),'TRADE LOG'!B:B,"<="&DATE(2021,2,28)),"!!!!!!","")

Could anyone suggest what needs to be added in order to achieve this?

To make this clearer, I want F16 in STATISTICS to count the number of W's in sheet TRADE LOG column P between the dates of 01 Feb 2021 and 28 Feb 2021. However, if there is none I would like F16 to return a blank.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Insider
  • 95
  • 12
  • 1
    What did you mean with "hardcoded"? Are you not working with true datevalues? Currently you `IF()` does not make sense if you have your `COUNTIFS()` in there. – JvdV Feb 05 '21 at 09:30
  • Hi JvdV, I assumed they were hardcoded, I stand corrected. I was under the impression I just needed to insert a formula in place of the '!!!!!!' , if that's not that case then I am unsure. – Insider Feb 05 '21 at 10:48
  • I think you can just use the `COUNTIFS()` as you have got it done. No need for `IF()`. – JvdV Feb 05 '21 at 11:05
  • It just returns 'error with this formula' and i can't figure out why this is. – Insider Feb 05 '21 at 12:37
  • Can you confirm whether or not you are working with true dates or just string values looking like dates? – JvdV Feb 05 '21 at 12:39
  • I have just edited the question, it should give a clearer direction on what I am trying to do. The dates are manually put in ones, I'm not sure if this makes them true or string values? – Insider Feb 05 '21 at 12:44
  • Excel version 16.45 – Insider Feb 05 '21 at 12:56
  • 1
    Oh right, 2019 version. – Insider Feb 05 '21 at 13:05
  • 1
    Typically you'd then use the `IF(=0,"",)` construct. Basically you'd do the same calculation twice. – JvdV Feb 05 '21 at 13:20
  • Thanks, that makes some sense to me. I initially thought that's what I had done above, however now when I attempt to write the formula the way I want things to come out I get the same error in the formula. I think i am struggling to implement it with the dates. – Insider Feb 05 '21 at 13:27
  • Your `COUNTIFS()` looks fine. This is why I mentioned *are you **sure** you are working with true datevalues* – JvdV Feb 05 '21 at 13:37
  • I'm not sure what you mean (i have tried to research this). I entered the dates manually into the formula if that's what you mean. – Insider Feb 05 '21 at 13:55
  • That's not what I mean. If everything in your formula seems correct but you still get an error it's time to look for answers elsewhere. For example in the data you are processing. Is **that** actually dates or just string values looking like dates? – JvdV Feb 05 '21 at 13:57
  • The dates are auto-populated via VBA formulas and fill cell A1 when I enter data into A2. I have done this due to requiring static dates. – Insider Feb 05 '21 at 14:05

0 Answers0