COUNTIFS with multiple criteria

[L40]

I need help trying to figure out a count formula. I’m tracking employee’s errors by date. The sheet where the data is at is named Annex Data. The formula will be on the sheet named Error Dashboard column “N” row 6. Here is what I need the formula to do.

I need it to count the number errors the employee had for a given date i.e. 8/18/15 by employee initials. It then needs to exclude the count if the corrected date in column M is #N/A or blank. I think it would something like this formula but I’m missing several pieces. =COUNTIFS('Annex Data'!$J:$J,"FAL01",'Annex Data'!$B:$B,$n$4).

1. Annex Data Column “B” contains the date the error was made
2. Annex Data Column “I” contains the employee initials i.e. “FAL01”
3. Annex Data Column “J” contains different codes i.e. Employee Error, Vendor Error, Accounting Error etc...
4. Annex Data Column “M” is the date the error was corrected
5. Error Dashboard N6 will contain the formula needed for above results
6. Error Dash board N4 contains a date i.e. 8/18/15

[daffodil11]

=COUNTIFS('Annex Data'!B:B,$N$4,'Annex Data'!I:I2,$I$2,'Annex Data'!M:M,"<>"&"#N/A",'Annex Data'!M:M,"")

[Aardigspook]

Hi,
Try this - slightly amended from the formula above :

Please Login or Register  to view this content.

It's best not to include entire columns in such formulae if you can avoid it as this slows the calculation down - select the range you need to check, plus some extra - I've used 1 to 1000.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[L40]

This will not work I'm still missing something almost like an IF statement. Remember I need it to return the criteria if Column "J" is tagged as employee error.

[L40]

I figured it out. here is the correct formula =COUNTIFS('Annex Data'!$B:$B,$O$4,'Annex Data'!$I:$I,"FAJ01",'Annex Data'!$M:$M,"<>"&"#N/A",'Annex Data'!$M:$M,"<>"&"",'Annex Data'!$J:$J,"Employee"). Turns out I needed to add ,'Annex Data'!$J:$J,"Employee" at the end of the formula. Thank you all for the help and guidance.

[Aardigspook]

Sorry, I missed the need to check for the 'Employee' error. Glad you got it figured out.

I still recommend you don't check entire columns ($B:$B for example) as that will slow your file down as it gets bigger - your formula as it is is checking 5 x 1048576 = 5,242,880 cells - if you changed to rows 1-1000 ($B1:$B1000 for example) it would only be checking 5000 cells which is 1000 times faster.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[Tony Valko]

I still recommend you don't check entire columns ($B:$B for example) as that will slow your file down as it gets bigger - your formula as it is is checking 5 x 1048576 = 5,242,880 cells

Not necessarily.

COUNTIF(S) is an efficient function.

If you use entire columns as references the function only calculates the used range. The used range is the actual range of cells that contain data.

For example, if the data goes down to row 100 then the used range ends at row 100.

So, these formulas would be equally efficient:

=COUNTIF(A1:A100,"X")
=COUNTIF(A:A,"X")