How do I count two conditions using a wildcard?

[NoNickName]

I need to know how to count by two conditions, using a wildcard for
one of those conditions. For instance, each member has a rank and a
unit. I can count multiple conditions without wildcards.

=SUM((C2:C152="E5")*(U2:U152="BN1"))

will tell me how many E5s are in 1st Battalion.

But how many enlisted personnel total are in 1st Battalion? Enlisted
ranks are indicated by E1, E2 and so on, up to E9. I've tried several
formula that replace the "E5" with "E*" or "E?", but none have
worked. Writing 9 different formula for E1 to E9, then totaling those
formula up would work, but is there an easy way to do this?

 

[Bernard Liengme]

You should learn about SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
then come back with question.
Give us more details of how the data is set out.

The asterisk (*) in your formula is not a "wildcard" it is the
multiplication operator.
best wishes

 

[Guest]

=SUMPRODUCT(--(LEFT(C2:154,1)="E")*(U2:U152="BN1"))