Using SUM/COUNTIF with multiple criteria and having the value update based on Auto Filter

[eman74]

This data is a simplified version to illustrate my question.

1 - Course -------- Name ---------- Elmement ---------- MOS ---------- Res/Reg
2 - 1111 ---------- Dave ------------ Land ------------ 1111 ----------- Reg
3 - 2222 ---------- Mike ------------- Land ------------ 6666 ----------- Reg
4 - 1111 ---------- Shelly ------------ Air -------------- 6666 ----------- Reg
5 - 1111 ---------- Dan -------------- Sea ------------- 1111 ----------- Res
6 -
7 - Total Reg Force Land:
8 - {=SUM((E2:E3000="Reg")*(C2:C3000="Land"))}

Above Formula works perfectly (result is 2)

My problem is that I want the results of the formula to update based on an autofilter in the above list.
So, for example, the user can select Course 1111 and the Forumula results = 1.

 

[Andrew Poulsom]

You can use SUMPRODUCT with SUBTOTAL. Here's a tutorial:

Excel Function Friday: Subtotal and Sumproduct with Filter | Contextures Blog