Counting Max Rows with Multiple Criteria to Check

[farrahsj]

If I have the below sample spreasheet of 10 rows and 4 columns where
Columns C and D can have different values:
(COLUMN#)
(ROW#)
A B C D
1 1 1 M 8
2 2 1 M 8
3 3 1 M 8
4 3 2 M 8
5 3 3 M 8
6 4 1 M 8
7 5 1 M 8
8 5 2 M 8
9 6 1 M 8
10 7 1 M 8

I want to come up with the COUNT of the number of entries that meet
the criteria:
--C1:C10=M
--D110=8
--IF A1:A10 = A1:10, Count only the MAX of B1:B10 (For example A3, A4,
and A5 = 3, so only count B5)

Thus, in my example, it would count Rows 1, 2, 5, 6, 8 , 9, 10 for a
Total Count of 7.

Any suggestions without using a Macro?

 

[T. Valko]

Here's my best guess...

Seems that all you need to do is count the unique numbers in col A where col
C =M and col D =8.

Try this array formula** :

=COUNT(1/FREQUENCY(IF((C1:C10="M")*(D110=8),A1:A10),A1:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[farrahsj]

That works perfectly. Thank You!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


That works perfectly. Thank You!