COUNTIF nested in SUMIFS, or complicated SUMPRODUCT

0
votes

Sample Data

I have a spreadsheet with column A being the names of my employees and all other columns are punch times. There are two columns per day (in and out). If someone takes a lunch, the entry will be 3:00|5:00 to indicate that they punched in at both 3am and 5am. Yes, that is a ridiculously silly report, but it's what I have. I want to count the number of times someone does NOT take a break (for legal reasons). So I have all this info on one sheet and then another index sheet with the employee names and this formula that I'm trying to write. I really need countifs to work more like sumifs. Meaning count the number of cells in any row where column A in that row matches a given name. Here are some failures that I have written:

=SUMPRODUCT(--('TS Summary'!A2:A10000=A3),--('TS Summary'!B2:ZZ10000="*|*"))/2

=SUMIFS('TS Summary'!A2:ZZ1000,'TS Summary'!A2:A1000,A4,'TS Summary'!B2:ZZ1000,"*|*")/2

=SUMIFS('TS Summary'!A2:A1000,'TS Summary'!A2:A1000,A7,'TS Summary'!A2:B1000,"*|*")/2

=COUNTIFS('TS Summary'!B2:ZZ10000,"*|*",'TS Summary'!A2:ZZ10000,A8)/2

The /2 at the end is because it would otherwise count both in and out but I those are always in pairs.

I think I've done enough research to know that only SUMPRODUCT is going to save the day here, but I don't know anything about SUMPRODUCT and after looking at over 100 examples on various sites and going 10 pages deep into Google searches, I think I can say that no one has documented how to do this. So I'm writing this firstly in hopes of getting some help, and secondly so that when it is resolved (by myself or with help) it will be documented for the next person who tries to tear out their hair!

For reference (and to make this searchable in the future), the HR system with these reports is Humanity. And some search terms might be sumproduct to sum a matrix where column A equals a specific term; and countifs nested in sumifs.

2
excel-formula
instead of trying to create a picture through words, please post a mock up of the data and expected output. Put it in as text in the original post using edit - Scott Craner
I agree with @ScottCraner. Please post some sample data and what you are trying to get out. Does this boil down to "I want a count of cells in a person's row that don't have a pipe | character in them (divided by two for in/out)?" - JNevill

2 Answers

1
votes

enter image description here

I'm assuming your data looks like columns A:E above, but much longer.

For the summary table, column G is a list of every employee. Column H is a lookup for that employee's row: =MATCH(G2,A:A) , and column I is a count of the times the "|" character appears in that employee's row, divided by two:

=SUMPRODUCT(LEN(INDIRECT("B"&H2&":E"&H2))-LEN(SUBSTITUTE(INDIRECT("B"&H2&":E"&H2),"|","")))/2

In this function, you'll need to edit the range within the indirect (the E, specifically) to be the last column in your table. Hope this helps!

0
votes

Okay, this didn't need to be as complicated as I made it. I got so caught up in the pure problem (reference a cell, compare a column to that cell, and sum all the | characters in the matching rows) that I overlooked the easy solution: A reference column! So I added a new column with this formula:

=COUNTIFS(C2:ZZ2,"|")/2

Then I simply used SUMIF as so:

=SUMIF('TS Summary'!B2:B500,Summary!A2,'TS Summary'!A2:A500)

Now my summary sheet searches column B for the name and prints the value of A, and column A does the counting of | characters.

Thank you for your help @Gattoun, I'm not sure how long I would have been working on this had I not seen your simplified drawing with everything in line (which sparked the idea for the solution)!