How to avoid counting same values in multiple countifs

Hi all I'm having a bit of a brain freeze today and wondering could someone point me in the right direction.

I have several COUNTIFS formulas in a single cell in a worksheet, all added together. The idea is that I am counting numbers of staff from a time & attendance worksheet where they fall under the criteria to be excused from this particular report. There is a lot of criteria and with the function I am using, for some departments I am getting more excused numbers of staff than actually exist in the department.

I think this is because some staff are being counted twice or more. Can someone point me to the correct logic I should be looking for in the COUNTIFS to ensure that the results of one COUNTIFS is not also counted in another?

Here's the formula I am using:

=COUNTIFS('DAA Exception Report'!C:C,Main!B9,'DAA Exception Report'!M:M,"<0.25",'DAA Exception Report'!M:M,"<>0")+COUNTIFS('DAA Exception Report'!C:C,Main!B9,'DAA Exception Report'!N:N,"<0.25",'DAA Exception Report'!M:M,"<>0")+COUNTIFS('DAA Exception Report'!C:C,Main!B9,'DAA Exception Report'!E:E,"*Flex*",'DAA Exception Report'!L:L,"Unmatched target")+COUNTIFS('DAA Exception Report'!C:C,Main!B9,'DAA Exception Report'!J:J,">"&'DAA Exception Report'!I:I)+COUNTIFS('DAA Exception Report'!C:C,Main!B9,'DAA Exception Report'!F:F,"Y",'DAA Exception Report'!L:L,"<>Missed a clocking")+COUNTIFS('DAA Exception Report'!C:C,Main!B9,'DAA Exception Report'!F:F,"Y",'DAA Exception Report'!L:L,"<>Swiped outside of shift & Missed a clocking & Unmatched target")+COUNTIFS('DAA Exception Report'!C:C,Main!B9,'DAA Exception Report'!F:F,"Y",'DAA Exception Report'!L:L,"<>Swiped outside of shift & Missed a clocking")+COUNTIFS('DAA Exception Report'!C:C,Main!B9,'DAA Exception Report'!F:F,"Y",'DAA Exception Report'!L:L,"<>Missed a clocking & Unmatched target")

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Can't you use the Unauthorised Absence column?

So then Calculation!D4 becomes:

Formula:

Please Login or Register  to view this content.

Can you add a helper column to your source file?

Hi.

Perhaps you should state the logical conditions which are to be used to generate that figure, rather than simply giving your current formula, since there may be both errors and inconsistencies in the logic you have used there.

I'm afraid that, just looking at it, it is not at all easy to guess what that logic is. However, I can tell you that in general, if you're not sure what you're doing then of course you can end up with such a result.

For example, if I had a list of 10 people, and I wanted to find out how many of those 10 did not own a car and also did not own a house, I could not do this by simply calculating how many did not have a car, then calculating how many did not have a house, and finally adding the two together. I don't know if that's the mistake you've made here, though I hope you can see why that approach fails logically.

Regards

That's very helpful. Many thanks.

Could I just ask for one final thing? Can you let me know what your manually-calculated expected result is, just so I know what I'm aiming for?

Regards

But can't you manually obtain your result by selective filtering on the appropriate columns?

Regards

No - I mean surely there's a way for you to manually calculate what the actual result should be! Even if it's a long-hand process using several steps and pen and paper if necessary.

Regards

Thanks. Can you just clarify, when you say:

Originally Posted by eoindub

1) Where Late clock ins are less than 15 mins (0.25 in decimal time) and do not equal zero
2) Where Early clock outs are less than 15 mins (0.25 in decimal time) and do not equal zero
3) Where Shift column (column E) contains the word "Flex" AND the corresponding row in column L is "Unmatched target"
4) Where worked hours (col J) is greater than target hours (col I)
5) Where Swap (col F) is equal to "Y" AND column L DOES NOT equal "Missed a clocking" or DOES NOT equal "Swiped outside of shift & Missed a clocking & Unmatched target" or DOES NOT equal "Swiped outside of shift & Missed a clocking" or DOES NOT equal "Missed a clocking & Unmatched target"

are these all "AND" conditions, so that all of 1) to 5) must be met to generate the count?

Or are they five separate "OR" conditions, so that we work out a total for each of 1) to 5) and then sum those?

I assume it's the latter since, if we just take condition 1), there are only 4 results in the entire sheet for which the Late Clock In is <0.25 and also <>0.

Regards