Sumproduct & count if with multiple criteria

Hi Excel forum,

I tried putting together the following code to countif only the visable cells if the following criteria is in the cells "P" "EL1" "EL2" & "EL3"

Please see code

Please Login or Register  to view this content.

But i get 0 unless P is in the column.

Please see attached workbook with outlined result.

Thanks

Jamidd

Here's one option:

J33 =SUMPRODUCT(SUBTOTAL(3,OFFSET(J2:J30,ROW(J2:J30)-MIN(ROW(J2:J30)),,1)),(J2:J30="P")+(J2:J30="EL1")+(J2:J30="EL2")+(J2:J30="EL3"))

Originally Posted by 63falcondude

Here's one option:

J33 =SUMPRODUCT(SUBTOTAL(3,OFFSET(J2:J30,ROW(J2:J30)-MIN(ROW(J2:J30)),,1)),(J2:J30="P")+(J2:J30="EL1")+(J2:J30="EL2")+(J2:J30="EL3"))

Thanks this seems to work!

Im guessing i just add like so to add more criteria =SUMPRODUCT(SUBTOTAL(3,OFFSET(J2:J30,ROW(J2:J30)-MIN(ROW(J2:J30)),,1)),(J2:J30="P")+(J2:J30="EL1")+(J2:J30="EL2")+(J2:J30="EL3")+(J2:J30="L1)) ??

I wonder if there would be a way to simple it down because i do have another table i would like to use this formula on but i have 13 possible grades for that table compared to the three in this table!