I have a column of data that contains a two letter identifier, which can sometimes be combined with another 2 letter indentifier: Example
Column D
AA
DC
DC,AA
how would I get countifs to count each occurance of AA or DC by itself?
I have a column of data that contains a two letter identifier, which can sometimes be combined with another 2 letter indentifier: Example
Column D
AA
DC
DC,AA
how would I get countifs to count each occurance of AA or DC by itself?
Would this work for you?
=COUNT(SEARCH("AA";D1:D3))
(Arrayed so when you type it, press ctrl+shift+enter instead of enter)
If you get an error message, try replacing ; with ,
Hi,
Can any of the strings you wish to count also form part of a diferent string? For example, are there any strings such as "XXDC1", or any other that may contain "DC" which you would not wish to form part of the count for "DC"?
If you can't be certain of this, that's fine. It's just that the solution is a little more complex in that case, so I thought I'd ask first, since if you could guarantee that the above is not possible then I could give you a solution right now.
Regards
If they are all two letters separated by commas then I assume you can safely use wildcards, e.g. to count "AA" try
=COUNTIF(D:D,"*AA*")
or if you want to use a cell reference as criterion, e.g. with AA in B2
=COUNTIF(D:D,"*"&B2&"*")
Audere est facere
Opps, for got to add, the AA, or DC is part of a second worksheet in cell F2. my countifs formula looks something like this:
=countifs(worksheet1($A$3:$A$200,worksheet2(a2),worksheet1($d$3:$d$200),worksheet2(f2)).
Using my suggestion then the last criterion would become this:
"*"&Worksheet2!F2&"*"
Thank you all for the help. Just got handed a complication for this item:
Worksheet 1
1 Column D:
2 AA
3 DC
4 DC/AA
5 DC(Q)
Worksheet2
Column F:
2 DC
=countifs(worksheet1($A$2:$A$200),worksheet2(a1),worksheet1($d$2:$d$200),worksheet2(f2))
I need countifs to add up each instance of DC, less the DC(Q) circumstance.
Try adding another criteria for "<>DC(Q)" or if that might be amongst other text make it "<>*DC(Q)*"
Thank you, that gave me the answer I needed
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks