Excel COUNTIFS cell equals range of cells AND cell from range equals another range

Question

What formula can I use that will count the cells in my first sheet (ALL DATA) ...

• where the cell in range 'ALL DATA'!C$10:C$1000 equals A2 (sheet: SUMMARY)
• and the cell in ALL DATA range N10:N1000 is equal to the range E2:E5 in sheet 'USERS' ?

Thanks

Answer 1

I'm assuming that for the second criterion you just want to count rows where the column N value is equal to one of E2:E5

Try this formula in Summary sheet

=SUMPRODUCT(COUNTIFS('ALL DATA'!C:C,A2,'ALL DATA'!N:N,USERS!E2:E5))

COUNTIFS returns an array of 4 values, one each for E2:E5, so SUMPRODUCT is used to sum that array to get the final result

Assumes all values in E2:E5 are different, otherwise rows may be double counted