Search code examples
excelcountifsumifs

Count ifs based on multiple criteria and date

I need help creating a countifs formula (maybe I need a sumifs, not sure) with multiple criterias.

I have an absentee spreadsheet where I track the following: "vacation" day (V), "sick" day (S) and "other" (O) daily on a monthly basis. I have my formula to add the monthly total count of all "V", "S" and "O" but want it to add only to "today's date."

My dates are on cell B1:AF1

The data are on cells B2:AF2, B3:AF3 and so forth

My total cell are on AG2,AG3 and so forth

So far I have the following formula: 

=COUNTIF(B2:AF2,"=O")+COUNTIF(B2:AF2,"=S")+COUNTIF(B2:AF2,"=V")

I need to add the TODAY function and that is where I am having the issue.

I do not work much with the count if formulas or the today function.

Solution

  • Try this standard (non-array) formula,

    =SUM(COUNTIFS(B2:AF2, {"S","V","O"}, B$1:AF$1, "<"&TODAY()))

    Fill down as necessary. I'm not entirely sure from your narrative whether that < should be a <= or not.

    In the following image, the S, V and O are counted from the left portion of the matrix (date in row 1 less than today) while the save notations on the right hal are discarded.

      HOLIDAYS_UP_TO_TODAY