Countifs: Multiple Criteria in Same Range with Other Criteria

Is there a way to count multiple criteria in the same range in a countifs function even though there are other criteria that needs to be considered.

Below is what I came up with but I need to add Math1, Science1, History1, and English2 [edited as first sample was too easy to solve/sample excel file will have other subjects in it. Sorry ] in the 5th criteria. (Note: Not the actual data as it is confidential but is very similar.)


=COUNTIFS(Raw!A:A,"Jul",Raw!F:F,"Yes",Raw!B:B,"No",Raw!C:C,"Teacher",Raw!D:D,"English1",Raw!E:E,"Teacher A")


Basically, I need it to count all English1, English2, English3, English4, and English5 in the same column but still consider all the other variables in the existing formula.

I was thinking nested formula but I can't figure out what function I should use and how to execute it. I also tried this

=COUNTIFS(Raw!A:A,"Jul",Raw!F:F,"Yes",Raw!B:B,"No",Raw!C:C,"Teacher",Raw!D:D,"English1",Raw!E:E,"Teacher A")+COUNTIFS(Raw!A:A,"Jul",Raw!F:F,"Yes",Raw!B:B,"No",Raw!C:C,"Teacher",Raw!D:D,"English2",Raw!E:E,"Teacher A")+COUNTIFS(Raw!A:A,"Jul",Raw!F:F,"Yes",Raw!B:B,"No",Raw!C:C,"Teacher",Raw!D:D,"English3",Raw!E:E,"Teacher A")+COUNTIFS(Raw!A:A,"Jul",Raw!F:F,"Yes",Raw!B:B,"No",Raw!C:C,"Teacher",Raw!D:D,"English4",Raw!E:E,"Teacher A")+COUNTIFS(Raw!A:A,"Jul",Raw!F:F,"Yes",Raw!B:B,"No",Raw!C:C,"Teacher",Raw!D:D,"English5",Raw!E:E,"Teacher A")

but this is too long and would take a lot of time since my data contains numerous teachers, subjects and numerous raw data file entry. Can someone help me with this?

Just change the 5th criteria to

"English*"

Use wildcard function. "B4"

Formula:

Please Login or Register  to view this content.

First of all your results table does not specify the subject. What are you actually trying to sum.

As requested the formula in B4 is

Formula:

Please Login or Register  to view this content.

Maybe a Pivot Table would be better - see attavhed