CountIf problem (2003 SP3)

Topic

New Reply

Good morning

I am currently using a number of CounIf formulas to add up the occurence of a letter in fields B5 – AQ5 and that is not a problem, what I would like to do though is to try and use the CountIf to add up H = 1 and h = 0.5 and still give me a total for instance if in the range B5 – AQ5 I had H occuring 5 times and h occuring 3 times the CountIf would = 6.5

Any ideas please

Cheers

Steve

Reply | Quote

Replies

Steve,
Is the ‘H’ or ‘h’ the sole contents of the cell, or are you counting any appearance of those letters within the cell contents? If they are the sole contents, then you can use:
=SUMPRODUCT(--EXACT($B$5:$AQ$5,"H"))

otherwise you would need a UDF I think – something like:

Public Function CountIfExact(rngInput As Range, strCriteria As String) As Long
   Dim rngCell As Range, lngCounter As Long
   For Each rngCell In rngInput
      If rngCell.Text Like strCriteria Then lngCounter = lngCounter + 1
   Next rngCell
   CountIfExact = lngCounter
End Function

Reply | Quote

Thanks Rory

There are a number of letters involved that at the moment get counted into different cells, these are H (Holiday), S (Sickness) etc. and I have been asked to further break down leave days into 1/2 days or full days, I thought that the easiest way would be a Capital representing a Full Day and a Lower case representing a 1/2 day.

Can you please exaplain in your first suggestion – =SUMPRODUCT(–EXACT($B$5:$AQ$5,”H”)) how that would determine H=1 or h=0.5, also what are the — before EXact for?

I have never used UDF’s so if possible I will avoid them for now as I cannot understand 1 part of your example as it does not mention H or h or 1 or 0.5 in it anywhere

Thanks for your attention

Cheers

Steve

Reply | Quote

Sorry, I should have been clearer – your complete formula would be:
=SUMPRODUCT(--EXACT($B$5:$AQ$5,"H"))+SUMPRODUCT(--EXACT($B$5:$AQ$5,"h"))*0.5
so that it counts ‘H’ and half of the ‘h’s
the -- is there to force conversion of TRUE/FALSE values to 1/0 respectively.
The UDF is generic, so you would have used:
=CountIfExact($B$5:$AQ$5,"h")*0.5+CountIfExact($B$5:$AQ$5,"H")
similar to the SUMPRODUCT formula. It sounds as though you don’t need that though if you only have one letter per day.

Reply | Quote

Thanks Rory

I will go and play with that

Cheers

Steve

Reply | Quote

I’ve been trying to figure out a similar Countif. However, I don’t always have cells with unique values. For example, I want to count all A’s in a cell. It could be ABC, BA, AC, or A. I never have more than 3 letters in a cell and the A can only appear once. I was going to use a lot of cells to come up with a series of formulas, but if you have a nice, elegant UDF (or other solution) I’d appreciate the help!

Reply | Quote

You can use:
=COUNTIF(B5:AQ5,"*A*")
adjusting the ranges as necessary, if you don’t need it to be case sensitive?

Reply | Quote

You can use a formula like this:

=COUNTIF(A1:A100,"*A*")

The asterisks * are wildcards that stand for “any number of characters (including none)”

Reply | Quote

Thanks Hans and Steve. I was making it much too hard!

Reply | Quote

The reply was from Rory, not Steve!

Reply | Quote

Actually, that was dense of me – for a find within other characters, just use FIND:
=SUMPRODUCT(--ISNUMBER(FIND("H",B5:AQ5)))

Reply | Quote