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

0
votes

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

1
excelexcel-formula
What does this mean "and the cell in ALL DATA range N10:N1000 is equal to the range E2:E5 in sheet 'USERS'"? How does one compare a range of 990 cells to a range of 3 cells? – JNevill
Hi JNevel ... sorry, bad explanation. I want to count the rows in All DATA where 2 parameters are met. 1: where cells in range N10:N1000 in sheet 'ALL DATA' equals any of the cells from in range E2:E5 in sheet 'USERS' - 2: where cell A2 from sheet 'SUMMARY' equals cells in range N10:N1000 in sheet 'ALL DATA'. – SMORF

1 Answers

1
votes

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