2

Recently i've encountered an issue with the subtotal array formula. To be more explicit, i have the following scenario:

  • In my workbook i have two different sheets: Statistics and Bugs
  • In the Bugs sheet i have a query which updates with the latest data available at that point
  • This query pulls data into a table named as Info from which i refer only to one column named: CreatedTime_UTC
  • Now, in the Statistics sheet i have two cells: Start date - (E2) and End Date - (E4)

The problem is that if i have in my query table a cell that has the same date as the Start date or End date, the following formula does not count that cell

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Info[CreatedTime_UTC],ROW(Info[CreatedTime_UTC])-MIN(ROW(Info[CreatedTime_UTC])),,1))*(Info[CreatedTime_UTC]>=E2)*(Info[CreatedTime_UTC]<=E4))

To be mentioned that the formula counts only visible cells. Is there a workaround, so that the formula can count cells that contain the same dates as declared in the Start date or End date cells?

 | Id    | CreatedTime_UTC |
 |-------|-----------------|
 | 33760 | 8/17/2018       |
 | 33742 | 8/16/2018       |
 | 33566 | 8/8/2018        |
 | 33558 | 8/7/2018        |
 | 33472 | 7/30/2018       |
 | 33466 | 7/27/2018       |

This is the first picture where i pull the data This is the table column where i have the dates listed

Adi Petrescu
  • 121
  • 1
  • 3
  • 12
  • Yes, table column CreatedTime_UTC is filtered from most recent date to oldest. I have attached 2 images. in the first image you can see that it shows **5** bugs instead of **6** according to the second image. – Adi Petrescu Aug 19 '18 at 14:41
  • I still get 6. Does pressing F9 make a difference? – QHarr Aug 19 '18 at 15:15
  • I've tried that, it doesn't... The only possibility (which might sound weird) is that i've created the document when i was in Europe and now i've traveled to America so maybe my laptop finds the time adjustment as an issue?.... could that be it? – Adi Petrescu Aug 19 '18 at 15:19
  • If you convert the dates to Longs do you have the same problem? – QHarr Aug 19 '18 at 15:21
  • Yes, i have also tried with custom date formats... the problem is the same – Adi Petrescu Aug 19 '18 at 15:27

0 Answers0