0

Table image of data with headings: Sr No. - Employee Name - Start Date - End Date - Total Days

How I see it what I need is =countif(C:C,"datedif(12-09-2015,C:C,"d")>56") My best guess is that within the datedif brackets I can't enter a date as C:C.

I keep getting an error message or a zero.

What I want to achieve is to have a monthly (1-30/31) table that shows how many people were working on that day that had been there for more than 56 days. So the end table would look something like this:

Table of end result: Showing days of month in x-axis and location in y-axis

IKavanagh
  • 6,089
  • 11
  • 42
  • 47
N.S.
  • 1
  • 1
  • How to place people in `Locations X` if `Location X` is not present in first table? – EEM Nov 05 '15 at 13:29

1 Answers1

0

use array =SUM(IF()) instead of =COUNTIF()

Firs of all create an IF finction that return TRUE when your conditions are met:

  • in cell F2 put: =IF("20/09/2015"-C2:C12 > 56) and confirm with CTRL+Shift+enter to create an array formula you should get: TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE
  • once you know it works as intended, you can just wrap it in a single-cell sum. In cell G2 put: =SUM(IF("20/09/2015"-C2:C12 > 56)) and confirm with CTRL+Shift+enter. You will have a single cell this time, with a result of 3

This should be enough to get you started. In your case I suggest you store the "1,2,3,4,5..." day numbers in results sheet as dates, formatted to display just the day number, and then reference that directly in each countif

user3616725
  • 3,485
  • 1
  • 18
  • 27