0

I am trying to count only times when column E matches a range on another sheet and column AA matches a different range on another sheet. I can count the times column E matches a cell in the first range with:

=SUMPRODUCT(COUNTIF('All Class Failure Report'!$E:$E,Classes!B1:N1))

But I can't count the times column AA matches the other range if the first condition is met.

My best guess so far is something like:

COUNTIF('All Class Failure Report'!$E:$E,(SUMPRODUCT(COUNTIF('All Class Failure Report'!$E:$E,Classes!B1:N1)) and (SUMPRODUCT(COUNTIF('All Class Failure Report'!$AA:$AA,Classes!B34:N34)))))

However this returns an error.

Question: can I get the count of times Row E contains value from a range and Row AA contains a value from a different range? Thanks!

uttuck
  • 51
  • 2
  • 9

1 Answers1

0

Have you tried using COUNTIFS (which allows mulitple criteria)?

=SUMPRODUCT(COUNTIFS('All Class Failure Report'!$E:$E,Classes!B1:N1,'All Class Failure Report'!$AA:$AA,Classes!B34:N34))
Andy
  • 49,085
  • 60
  • 166
  • 233
Joe
  • 92
  • 4