I have an Excel spreadsheet where I track firewall rule requests. Column B in the spreadsheet tracks the status of requests that have been submitted. A request can have a status of "modified", "pending", "approved", "denied", "completed", or "implement removal". Column G contains the dates when a request was either approved or denied. I could calculate how many requests I approved or denied since a particular date by putting that date in cell F947. I use a COUNTIFS function, which allows you to count the number of instances where an entry in a spredsheet meets multiple criteria. E.g., the value in column B for a row is greater than 10 and, also, the value in column C is "Jane". I use the formula
=COUNTIFS(G2:G932,">="&F947, B2:B932,"Denied")
to calculate all the entries that have been denied since a particular date.
E.g., if cell F947 contains the date 8/10/2016, I can count all the rows
starting with G2, since the first row is a header row, up through the last
entry where the date in column 2 is greater than or equal to August 10, 2016
and the status in column B is "Denied". I can also calculate the number of
requests I've dealt with since that date with
=COUNTIF(G2:G932,">="&F947), i.e., I have Excel count
any entries where the date is greater than or equal to the date
specified in cell F947. I can also count the number of requests
with a particular status that indicates the request was approved,
but then might have been implemented, which would send it to the
"Completed" state or which had been approved, but, because the
approval was for the removal of rules from firewalls, went to the
"Implement Removal" state with the following:
=COUNTIFS(G2:G931,">="&F947,B2:B931,"Approved")
=COUNTIFS(G2:G931,">="&F947,B2:B931,"Completed")
COUNTIFS(G2:G931,">="&F947,B2:B931,"Implement Removal")
All of the above indicate that the request was approved, though the status for the request might not be listed as "Approved" in the system used to manage the requests, but could, instead be shown as "Completed" or "Implement Removal".
But I also wanted to have a cell where Excel would provide a total for the number of requests that were in any of the above states where the date for the request in the G column was on or after the specified date. To do so, I had to "sum" multiple "countifs" statements, since I couldn't find a way to easily put an "and" or "or" logical condition within a countifs statement. I was able to get the total with the below forumula:
=SUM(COUNTIFS(G2:G931,">="&F947,B2:B931,"Approved"), COUNTIFS(G2:G931,">="&F947,B2:B931,"Completed"), COUNTIFS(G2:G931,">="&F947,B2:B931,"Implement Removal"))
