MoonPoint Support Logo


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals

Advanced Search
Sun Mon Tue Wed Thu Fri Sat

Sun, Aug 14, 2016 10:41 pm

COUNTIFS with an "or" condition

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,"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"))

[/software/office] permanent link

Once You Know, You Newegg AliExpress by

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo