Countif or countifs with multiple critera

[pcarmour]

Hi,
I am trying to count each occurrence of multiple criteria in a column. the formula is something like:

=countif(G2:G500,"GAU ","JUST ","SIMI1","SIMI2","SIMI3","SIMI4","SIMI5","SSC ","SSL ","SSZ ","TRO ","ZAILF","HBXO ","WAS ","GGKXO","BACCC")

I have also tried countifs but cant get it right.

Any help would be much appreciated.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)
Regards,
Peter.

[mancubus]

hi.try

=SUMPRODUCT(COUNTIF(G2:G500,{"GAU ";"JUST ";"SIMI1";"SIMI2";"SIMI3";"SIMI4";"SIMI5";"SSC ";"SSL ";"SSZ ";"TRO ";"ZAILF";"HBXO ";"WAS ";"GGKXO";"BACCC"}))

[pcarmour]

Hi Thank you for the quick reply. I'm sorry it hasn't worked. Please see attachedTest Countif.xlsx

[mancubus]

you are welcome. some cell values contain trailing spaces. you may remove extra spaces with trim function. (in this case remove the extra spaces from criteria.) or try this:

=SUMPRODUCT(COUNTIF(G2:G500,{"GAU ";"JUST ";"SIMI1";"SIMI2";"SIMI3";"SIMI4";"SIMI5";"SSC ";"SSL ";"SSZ ";"TRO ";"ZAILF";"HBXO ";"WAS ";"GGKXO";"BACCC"}&" "))

[pcarmour]

Hi Mancubus, Brilliant, that works perfectly, thank you very much.

[mancubus]

you are welcome. credits go to those who first used and made public the formula and such.

[pcarmour]

Hi mancubus,

I don't know what I'm doing wrong but the second group I need to count isn't working. It looks exactly the same coding. Please have a look at the attached.
Thanks again for your help.
Test Countif.xlsx

[mancubus]

Hi.

in this case below formula works.

=SUMPRODUCT(COUNTIF(G3:G467,{"HBA1 ";"HBA2 ";"HBA3 ";"HBA4 ";"HBA5 ";"HBA6 ";"HBA7 ";"HBA8 ";"HBA9 ";"HBAF ";"VANT1";"VANT2";"VANT3";"VANT4";"VANT5";"BEAUA";"BEAUM";"BEAUX"}))

considering the values in cells ar of the same kind with (ie, some values have trailing spaces) the previous formula, i dont know what makes the difference.

[mancubus]

i would remove extra spaces with the following code:
see the attached file.

Sub TrimXcessSpaces()
    'http://www.vbaexpress.com/kb/getarticle.php?kb_id=616
    
    'Macro Purpose: To trim all excess spaces out of cells.  This
    'eliminates issues where users have cleared the cell with a space,
    'and elimates all extra spaces at the beginning or end of a string
    
    Dim cl As Range
    
    'Loop through cells removing excess spaces
    For Each cl In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
        If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
            cl.Value = WorksheetFunction.Trim(cl)
        End If
    Next cl

End Sub

[pcarmour]

Hi Mancubus,

Yes it's a space issue, I have just used 'replace' to remove all spaces and -success- it all works. Thank you for pointing me in the right direction.

[mancubus]

you are welcome.

there may be occasions where you should consider keeping the "real" spaces (ie, multiple words in one cell) and "replace all" will remove these spaces as well.

above procedure ensures the removal of extra (leading, trailing and middle) spaces.