How do I count two conditions using a wildcard?
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? |
How do I count two conditions using a wildcard?
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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "NoNickName" wrote in message oups.com... 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? |
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? |
All times are GMT +1. The time now is 06:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com