PDA

View Full Version : [SOLVED] Countif or countifs with multiple critera



pcarmour
11-14-2013, 08:25 AM
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
11-14-2013, 08:40 AM
hi.try
=SUMPRODUCT(COUNTIF(G2:G500,{"GAU ";"JUST ";"SIMI1";"SIMI2";"SIMI3";"SIMI4";"SIMI5";"SSC ";"SSL ";"SSZ ";"TRO ";"ZAILF";"HBXO ";"WAS ";"GGKXO";"BACCC"}))

pcarmour
11-14-2013, 08:54 AM
Hi Thank you for the quick reply. I'm sorry it hasn't worked. Please see attached10829

mancubus
11-14-2013, 09:00 AM
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
11-14-2013, 09:05 AM
Hi Mancubus, Brilliant, that works perfectly, thank you very much.:beerchug:

mancubus
11-14-2013, 09:16 AM
you are welcome. credits go to those who first used and made public the formula and such. :)

pcarmour
11-14-2013, 10:40 AM
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.
10830

mancubus
11-14-2013, 12:26 PM
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
11-14-2013, 12:40 PM
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
11-14-2013, 01:01 PM
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
11-14-2013, 01:18 PM
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.