0

I am trying to make cell G5 SUM or COUNTIF (not sure which is better) the number of OPEN's and CLOSED's in column A on sheet SPREADS LOG between the date of 1 June 21 and 30 June 21.

However, if there is no OPEN or CLOSED between this date range I would like G5 to return a blank.

This is the current formula in G5

=IF(COUNTIFS('SPREADS LOG'!A:A,"OPEN",'SPREADS LOG'!R:R,">="&DATE(2021,2,1),'SPREADS LOG'!R:R,"<="&DATE(2021,2,28)),COUNTIFS('SPREADS LOG'!A:A,"CLOSED",'SPREADS LOG'!R:R,">="&DATE(2021,2,1),'SPREADS LOG'!R:R,"<="&DATE(2021,2,28)),"")

If anyone could tweak this that would be great.

Insider
  • 95
  • 12

2 Answers2

0

In my data, I have status in Column A and Date in Column B.

Formula in cell E2:

=IF(SUM(COUNTIFS(A:A,{"open","closed"},B:B,">="&DATE(2021,2,1),B:B,"<="&DATE(2021,2,28)))=0,"",SUM(COUNTIFS(A:A,{"open","closed"},B:B,">="&DATE(2021,2,1),B:B,"<="&DATE(2021,2,28))))

enter image description here

Isolated
  • 5,169
  • 1
  • 6
  • 18
  • Hi Isolated, I have tried that although something is a miss because I am getting "there is an error in your formula" -- ```=IF(COUNTIFS('SPREADS LOG'!A:A,"OPEN",'SPREADS LOG'!R:R,">="&DATE(2021,3,1),'SPREADS LOG'!R:R,"<="&DATE(2021,3,31)),COUNTIFS('SPREADS LOG'!A:A,"CLOSED",'SPREADS LOG'!R:R,">="&DATE(2021,3,1),'SPREADS LOG'!R:R,"<="&DATE(2021,3,31)),"")``` is what i am currently using. – Insider Feb 17 '21 at 15:53
  • Other than changing your columns to the right ranges and prefixing with the correct sheet name, it should work. Worked for me as illustrated in the image. Hopefully you'll get it working with one of the answers posted here. – Isolated Feb 17 '21 at 15:58
0

Your formula is very close, but I do suggest a couple changes to get what you want.

First is to establish a couple of cells somewhere (same sheet or a different sheet) that define your Start and Finish dates. This way, you don't have to rewrite your formula if you need to change the check range for the dates. So...

Cell Z1 = 6/1/2021
Cell Z2 = 6/30/2021

Then your counting function becomes:

COUNTIFS('SPREADS LOG'!A:A,"OPEN",'SPREADS LOG'!R:R,">="&Z1,'SPREADS LOG'!R:R,"<="&Z2)

After that, it's just a simple addition:

=COUNTIFS('SPREADS LOG'!A:A,"OPEN",'SPREADS LOG'!R:R,">="&Z1,'SPREADS LOG'!R:R,"<="&Z2)+COUNTIFS('SPREADS LOG'!A:A,"CLOSED",'SPREADS LOG'!R:R,">="&Z1,'SPREADS LOG'!R:R,"<="&Z2)

Since you want to show a zero count as blank, set the custom number format of the cell to 0;-0;;@ (more information here). This avoids making your formula overly complicated.

PeterT
  • 8,232
  • 1
  • 17
  • 38
  • Hi PeterT thanks for that, however, I have written all of my dates into multiple formulas so I will keep it that way now, I will remember for next time though! Also, I cannot use the 0 hiding format as at the bottom I am trying to average and the 0 gives an inaccurate rading. – Insider Feb 17 '21 at 15:27