G Sheets: Count 1 per row IF Col D <> blank but Col E = blank OR F <> blank but G is, etc.

This is a check out / check in table for keys to an office building. Each row is a new person who has checked out keys. I want to calculate how many rows have at least one outstanding item. (something with a value for the cell for being checked out, but not yet a value for being checked in)

The closest I have come is the following formula:

Please Login or Register  to view this content.

The table below roughly represents my spreadsheet. Joe has an outstanding key, so that row should count as one. Bill has turned in all his keys. That counts as 0. Jim has two keys outstanding. That row counts as 1. Bob only checked out one set of keys, but didn't turn them in. His row should count as 1. The sum is the number of people with at least one set of outstanding keys, in this case 3.

The problem with the above formula, is that a row with one set of turned in keys counts as 0, even if another set is outstanding. (Joe's row) The second problem with the above formula is that all sets of keys must be checked out, or else it counts as 0 -- even if just one set of keys is checked out, but not yet returned. (Bob's row) Using the above formula on my example table, Joe and Bob both count as 0, making the # of people with outstanding keys show incorrectly as 1. In other words, the only rows my formula correctly calculates is rows 6 and 7.

	Col A	Col B	Col C	Col D	Col E	Col F	Col G
...4	First Name	Last Name	Group	Master Key Check Out	Master Key Check In	Other Key Check Out	Other Key check In
5	Joe	Smith	Manager	2/2/2020	3/2/2020	2/2/2020
6	Bill	Jones	Employee	2/2/2020	2/3/2020	2/2/2020	2/3/2020
7	Jim	Williams	Employee	2/2/2020		2/2/2020
8	Bob	Robertson	Employee	2/2/2020

IMPORTANT DETAILS

• This is a Google Sheets document.
• The attached xlsx document does not display the same as the Google Sheet does, but it is a direct download from Google Sheets to an xlsx file.
• The equation in question is found in cell L1 of the attached document and Google Sheets
• The spreadsheet attachment has the link to the actual Google Sheets document in it, found in cell L5. Fiddle with the Google Sheet by choosing File -> Make a Copy. You will then have a copy of the file you can edit as you wish.

Summary: Using the example above... Counting people who have a set of keys checked out, but not checked back in, rows 5, 7, and 8 should each count toward a sum of 3.

I appreciate any assistance. Thank you.

Hi and welcome to the forum,

Here's a suggestion using a helper column.
In Q5, you could write this formula: =IF(A5<>"",IF(COUNTA(D5,F5,H5,J5)>COUNTA(E5,G5,I5,K5),1,0),0)
Copy it down
then make the sum of that column.

The logic is the following: if you have a name in A, and the count of OUT dates is smaller than IN dates, then you count as a person with outstanding keys.

Originally Posted by GC Excel

Hi and welcome to the forum,

Here's a suggestion using a helper column.
In Q5, you could write this formula: =IF(A5<>"",IF(COUNTA(D5,F5,H5,J5)>COUNTA(E5,G5,I5,K5),1,0),0)
Copy it down
then make the sum of that column.

The logic is the following: if you have a name in A, and the count of OUT dates is smaller than IN dates, then you count as a person with outstanding keys.

This was a beautiful way to reframe the problem. Based off your answer, I came up with a new formula which looks pretty similar to my original formula. Please let me know if you notice a problem with my formula, or if there is a better way to do this. However, as far as I can tell, it is working wonderfully. Here's what I came up with.

Please Login or Register  to view this content.

I think the logic here is the same. The execution is just slightly different. SUMPRODUCT is an array formula, I believe. So it is essentially looping through each row. I believe Google Sheets is interpreting each range<>"" as a True or False represented by a 1 or a 0. TRUE/1 if the cell in question is not blank. FALSE/0 if it is blank. So, it adds those ones and zeros on each side of the logical operator ">". Then, it is interpreting the entire logical expression "(((D5:D<>"")+(F5:F<>"")+(H5:H<>"")+(J5:J<>""))>((E5:E<>"")+(G5:G<>"")+(I5:I<>"")+(K5:K<>"")))" as a True or False boolean represented by a 1 or a 0. That gets multiplied by (A5:A<>""), which will also be a 1 or a 0. 1 if there is a name, 0 if there is no name (making that row 0)

So, for my Joe Smith example, it breaks down like this:

Please Login or Register  to view this content.

Then, it is doing this for each row, creating an array:

Please Login or Register  to view this content.

Adding those results together as the final step

Please Login or Register  to view this content.

Producing the desired result.