View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Toppers Toppers is offline
external usenet poster
 
Posts: 4,339
Default How do I count two conditions using a wildcard?

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

"NoNickName" wrote:

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?