COUNTIFS( where one criteria compares a column to another column)

[corecomps]

I have a row of data that (with other columns) have date/times in them. One is the date a person should have clocked in and the other is a column when they actually clocked in. I'm filtering the rows to count based on other factors as well like A row storing location or a row storing the payroll dollar amount. See below:

P = the location
A24 = is a cell with each location in it
AF = is payroll, I only care about rows with payroll paid in it
G = actual clock out time
M = expected clock out time

Result = I want to count the total number of "late clock ins" by location.

What I have so far:
=COUNTIFS('Care Log Raw Data'!P:P,Calculator!A24,'Care Log Raw Data'!AF:AF,">0",'Care Log Raw Data'!G:G, "<" & 'Care Log Raw Data'!M:M)

The final red selection is the part that isn't working.

I can see examples where people are using "SUMPRODUCT" for this situation but they are not also considering other criteria like my filtering on P or AF.

I'm open to other ways to get this data. What I can't do is create a new column of data in that data workbook called "Care Log Raw Data".....that is because it is imported by a resource that wouldn't know how to correctly add that column when they pull these reports.

[GeoffW283]

Welcome to the forum

I can't see anything wrong with your formula. I created the equivalent on my computer and that worked as expected.

You say "The final red selection is the part that isn't working." How is it not working? Error return?, wrong value?

It may help if you upload a small but representative subset of your workbook (not a screenshot) with any sensitive/proprietary data removed that illustrates the problem.

To attach a workbook, click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.

[XOR LX]

I can't see anything wrong with your formula. I created the equivalent on my computer and that worked as expected.

Then you were very lucky! The posted formula does not at all perform a row-by-row comparison. In fact, when computing:

=COUNTIFS('Care Log Raw Data'!P:P,Calculator!A24,'Care Log Raw Data'!AF:AF,">0",'Care Log Raw Data'!G:G, "<" & 'Care Log Raw Data'!M:M)

Excel reduces the entire column's worth of values within:

'Care Log Raw Data'!M:M

to just a single cell, i.e.:

'Care Log Raw Data'!M1

I assume in your mock set-up it was pure coincidence that every value in 'Care Log Raw Data'!G:G just happened to be less than your chosen value in 'Care Log Raw Data'!M1.

@corecomps

You need to switch to SUMPRODUCT in such cases, though of course then you would be strongly advised to not use entire column references, e.g.:

=SUMPRODUCT(0+('Care Log Raw Data'!P1:P100=Calculator!A24),0+('Care Log Raw Data'!AF1:AF100>0),0+('Care Log Raw Data'!G1:G100<'Care Log Raw Data'!M1:M100))

Regards

[corecomps]

Thanks for the help GeoffW283

I think it is working. I have a situation however that is odd. With this data the final row. M is the last column and G is the first column in this data sample.
There are 13 rows
if I use "=" I get 0
if I use "<" I get 12
if I use "<=" I get 12
if I use ">" I get 0
if I use ">=" I get 0

Any thoughts?

=SUMPRODUCT(0+('Care Log Raw Data'!P:P=Calculator!A26),0+('Care Log Raw Data'!AF:AF>0),0+('Care Log Raw Data'!G:G>='Care Log Raw Data'!M:M))

Actual Clock In Actual Clock Out Official Clock In Date Official Clock In Time Official Clock Out Date Official Clock Out Time Scheduled Clock In
04/14/2019 06:13 AM 04/14/2019 01:30 PM 04/14/2019 06:15 AM 04/14/2019 01:30 PM 04/14/2019 06:30 AM
04/14/2019 03:58 PM 04/14/2019 09:00 PM 04/14/2019 04:00 PM 04/14/2019 09:00 PM 04/14/2019 04:00 PM
04/15/2019 06:14 AM 04/15/2019 01:47 PM 04/15/2019 06:15 AM 04/15/2019 01:45 PM 04/15/2019 06:30 AM
04/15/2019 03:52 PM 04/15/2019 09:00 PM 04/15/2019 03:45 PM 04/15/2019 09:00 PM 04/15/2019 04:00 PM
04/16/2019 06:16 AM 04/16/2019 01:45 PM 04/16/2019 06:15 AM 04/16/2019 01:45 PM 04/16/2019 06:30 AM
04/17/2019 06:14 AM 04/17/2019 01:22 PM 04/17/2019 06:15 AM 04/17/2019 01:30 PM 04/17/2019 06:30 AM
04/17/2019 03:49 PM 04/17/2019 08:57 PM 04/17/2019 03:45 PM 04/17/2019 09:00 PM 04/17/2019 04:00 PM
04/18/2019 06:14 AM 04/18/2019 01:30 PM 04/18/2019 06:15 AM 04/18/2019 01:30 PM 04/18/2019 06:30 AM
04/19/2019 06:14 AM 04/19/2019 01:44 PM 04/19/2019 06:15 AM 04/19/2019 01:45 PM 04/19/2019 06:30 AM
04/19/2019 03:58 PM 04/19/2019 09:14 PM 04/19/2019 04:00 PM 04/19/2019 09:15 PM 04/19/2019 04:00 PM
04/20/2019 06:14 AM 04/20/2019 01:44 PM 04/20/2019 06:15 AM 04/20/2019 01:45 PM 04/20/2019 06:30 AM
04/20/2019 03:55 PM 04/20/2019 09:01 PM 04/20/2019 04:00 PM 04/20/2019 09:00 PM 04/20/2019 04:00 PM
04/20/2019 12:00 AM 04/20/2019 11:59 PM 04/20/2019 12:00 AM 04/20/2019 11:59 PM 04/20/2019 12:00 AM

[XOR LX]

Not sure who you're replying to here. Seems to be to GeoffW283, though you then go on to cite a version of the formula I posted (which, incidentally, contains references to entire columns, which I strongly recommend against, as already mentioned).

Regards

[corecomps]

Sorry XOR LX, I was referencing you and put in the wrong name.

I'm using the full column right now for testing but also because I will have no knowledge of the number of rows coming in. I suppose I could limit it to the max number of rows I would ever expect. I should never have more than 5000.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[corecomps]

Never mind, the formula is working. I found out the display was only going to the second but the clock in was actually 4:00:06 vs 4:00. 6 onehundreds of a second late!

Thank you all for your help!

[GeoffW283]

@XOR LX re: your post #3. You are, of course, exactly right. I had made a simple 3-row test and I had use the same clock-out time for each row - a poor test.