Help with a formula please - CountIf formula to return No. of HL instances in multiple cells

[EmmaN]

Hi all,

I'm trying to enter a Countif formula to add up the number of instances 'HL' appears in a number of cells across a row, but it keeps returning an error message, the error says i have used too many arguments for this function.

The formula i am trying to use is =COUNTIF(J3,R3,AA3,AJ3,AW3,BE3*"HL*")

[TABLE="width: 1203"]
<tbody>[TR]
[TD][/TD]
[TD]i[/TD]
[TD]J[/TD]
[TD]R[/TD]
[TD]AA[/TD]
[TD]AJ[/TD]
[TD]AW[/TD]
[TD]BE[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] Total number of HL[/TD]
[TD] Option 1 (IB Group 1)[/TD]
[TD]Option 2 (IB Group 2)[/TD]
[TD]Option 3 (Mixed IB Group)[/TD]
[TD]Option 4 (IB Group 4)[/TD]
[TD]Option 5 (IB Group 5)[/TD]
[TD]Option 6 (Mixed IB group)[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] =COUNTIF(J3,R3,AA3,AJ3,AW3,BE3*"HL*")

This should return 3[/TD]
[TD]SL English[/TD]
[TD]SL Spanish[/TD]
[TD]Group 3: SL Business Management[/TD]
[TD]HL Physics[/TD]
[TD]HL Mathematics[/TD]
[TD]Group 4: HL Chemistry[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] =COUNTIF(J4,R4,AA4,AJ4,AW4,BE4*"HL*")

This should return 3[/TD]
[TD]SL English Language and Literature[/TD]
[TD]SL French[/TD]
[TD]Group 3: HL Psychology[/TD]
[TD]HL Design & Technology[/TD]
[TD]SL Mathematics[/TD]
[TD]Group 4: HL Biology[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]SL English Language and Literature[/TD]
[TD]HL Spanish[/TD]
[TD]Group 3: SL Economics[/TD]
[TD]HL Biology[/TD]
[TD]SL Mathematics[/TD]
[TD]Group 4: HL Chemistry[/TD]
[/TR]
</tbody>[/TABLE]

This is driving me nuts, It should be really easy but i cant get it to work. Thank you. Emma

 

[rickjason]

on your criteria use "*"&HL&"*"

 [COLOR=#333333][FONT=Verdana]=COUNTIF(J3:AW3,[/FONT][/COLOR]"*"&HL&"*")

 

[EmmaN]

on your criteria use "*"&HL&"*"

 [COLOR=#333333][FONT=Verdana]=COUNTIF(J3,R3,AA3,AJ3,AW3,[/FONT][/COLOR]"*"&HL&"*")

Hi, rickjason, thank you so much for your reply, however this still retunrs the error 'you've entered too many arguments for this function.

Emma

 

[rickjason]

Hi, rickjason, thank you so much for your reply, however this still retunrs the error 'you've entered too many arguments for this function.

Emma

Countif if for range of cell only not selected cells otherwise, you can use this one.

=IF(ISNUMBER(SEARCH("HL",J3)),1,0)+IF(ISNUMBER(SEARCH("HL",R3)),1,0)+IF(ISNUMBER(SEARCH("HL",AA3)),1,0)+IF(ISNUMBER(SEARCH("HL",AJ3)),1,0)+IF(ISNUMBER(SEARCH("HL",AW3)),1,0)+IF(ISNUMBER(SEARCH("HL",BE3)),1,0)

 

[Peter_SSs]

If the other headings in row 2 do not contain the text "Option" then ..
- If you are using Excel 2010 or later
=COUNTIFS(J$2:BE$2,"*Option*",J3:BE3,"*HL*")

- or for any version of Excel
=SUMPRODUCT(--ISNUMBER(SEARCH("Option",J$2:BE$2)),--ISNUMBER(FIND("HL",J3:BE3)))

 

[EmmaN]

Countif if for range of cell only not selected cells otherwise, you can use this one.

=IF(ISNUMBER(SEARCH("HL",J3)),1,0)+IF(ISNUMBER(SEARCH("HL",R3)),1,0)+IF(ISNUMBER(SEARCH("HL",AA3)),1,0)+IF(ISNUMBER(SEARCH("HL",AJ3)),1,0)+IF(ISNUMBER(SEARCH("HL",AW3)),1,0)+IF(ISNUMBER(SEARCH("HL",BE3)),1,0)

Perfect, Thank you so much for your help. Emma

 

[Peter_SSs]

If the other headings in row 2 do not contain the text "Option" then ..
- If you are using Excel 2010 or later
=COUNTIFS(J$2:BE$2,"*Option*",J3:BE3,"*HL*")

- or for any version of Excel
=SUMPRODUCT(--ISNUMBER(SEARCH("Option",J$2:BE$2)),--ISNUMBER(FIND("HL",J3:BE3)))

Just to clarify, it doesn't have to be the word Option. If there is anything in the headings of these 'columns of interest' that does not appear in row 2 of any of the in-between columns (eg "Group" or just "(") then that could be used in either of my formulas instead of "Option".


If, for some reason you are forced to use the much longer way of checking individual cells, or you just choose to do that, the suggested formula can be written more simply as

=ISNUMBER(SEARCH("HL",J3))+ISNUMBER(SEARCH("HL",R3))+ISNUMBER(SEARCH("HL",AA3))+ISNUMBER(SEARCH("HL",AJ3))+ISNUMBER(SEARCH("HL",AW3))+ISNUMBER(SEARCH("HL",BE3))