COUNTIFS driving me crazy

[Carlos Ruano]

Hi, please help with the attached file.

This starts out as an exported report. I inserted a new helper column (A), and entered a formula to help find unique monitor names (column L). Then, I added columns AC:AH. In AC2:AC14, I entered a formula to list unique monitor names (also used in AE2:AE14 & AG2:AG14).

Now to the real question: I want to count the number of times each monitor (column L) has a >5 in column U. I want this formula in AD2:AD14. As you can see, I already made an attempt, but failed miserably Please help!

Thanks!
Carlos

 

[Luke M]

Hi Carlos.

Your formula had a mix of structural references and an entire column reference. The structural reference, for the table would only be 243 rows. Worse, with the "@" symbol in front, it was only looking at a single row. So, error was causes because the two arrays were different sizes. But, you were on the right track!

Correct formula:
=COUNTIFS(Table2[Monitor Name],AC2,Table2[(MVR Submission) '# of Bus days],">5")

 

[Monty]

Try this!

https://www.techonthenet.com/excel/formulas/countifs.php

 

[Carlos Ruano]

Hi Carlos.

Your formula had a mix of structural references and an entire column reference. The structural reference, for the table would only be 243 rows. Worse, with the "@" symbol in front, it was only looking at a single row. So, error was causes because the two arrays were different sizes. But, you were on the right track!

Correct formula:
=COUNTIFS(Table2[Monitor Name],AC2,Table2[(MVR Submission) '# of Bus days],">5")

Thank you! Your solution works perfectly. I was unaware that the "@" would mean that it was only looking at one row. Lesson learned!

 

[Carlos Ruano]

Try this!

https://www.techonthenet.com/excel/formulas/countifs.php

Thank you, Monty, for the excellent resource!