I would like to use the countif function to count a letter a a half ".5"
instead of as a whole "1." The application that it would be used for is on a
payroll workbook. i.e. "P" would eaqul a whole personal day whereas a "HP"
would be a half of a personal day. From there I would like to add all of the
"P's" and "HP's" in one Cell. this would be like 4.5 days used compared with
4 days used (P,P,P,P, HP)=4.5
Thanks,

Brandon

Hi Brandon,

To count a letter as a half using the COUNTIF function, you can use a nested IF function. Here's how you can do it:

1. Open your payroll workbook and select the cell where you want to display the total number of days used.
2. Type the following formula into the cell:
   Formula:

   =COUNTIF(A1:A10,"P")+IF(COUNTIF(A1:A10,"HP")0,0.5*COUNTIF(A1:A10,"HP"),0) 

3. In the formula, A1:A10 is the range of cells where you have recorded the personal days and half personal days used.
4. The first part of the formula, COUNTIF(A1:A10,"P"), counts the number of cells that contain the letter "P".
5. The second part of the formula, IF(COUNTIF(A1:A10,"HP")0,0.5*COUNTIF(A1:A10,"HP") ,0), checks if there are any cells that contain the letters "HP". If there are, it multiplies the count of "HP" cells by 0.5 to get the half days used, and adds it to the count of "P" cells. If there are no "HP" cells, it adds 0 to the count of "P" cells.
6. Press Enter to calculate the formula and display the total number of days used.

One way

=SUMPRODUCT(COUNTIF(A1:A100,{"P";"HP"}),{1;0.5})

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom



"ryanjh79" wrote:

I would like to use the countif function to count a letter a a half ".5"
instead of as a whole "1." The application that it would be used for is on a
payroll workbook. i.e. "P" would eaqul a whole personal day whereas a "HP"
would be a half of a personal day. From there I would like to add all of the
"P's" and "HP's" in one Cell. this would be like 4.5 days used compared with
4 days used (P,P,P,P, HP)=4.5
Thanks,

Brandon

I like that one Peo. Do you mind if I add that to the examples on the
SUMPRODUCT page?

Bob

"Peo Sjoblom" wrote in message
...
One way

=SUMPRODUCT(COUNTIF(A1:A100,{"P";"HP"}),{1;0.5})

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom



"ryanjh79" wrote:

I would like to use the countif function to count a letter a a half ".5"
instead of as a whole "1." The application that it would be used for is
on a
payroll workbook. i.e. "P" would eaqul a whole personal day whereas a
"HP"
would be a half of a personal day. From there I would like to add all
of the
"P's" and "HP's" in one Cell. this would be like 4.5 days used compared
with
4 days used (P,P,P,P, HP)=4.5
Thanks,

Brandon

Not at all.


Regards,

Peo Sjoblom



"Bob Phillips" wrote:

I like that one Peo. Do you mind if I add that to the examples on the
SUMPRODUCT page?

Bob

"Peo Sjoblom" wrote in message
...
One way

=SUMPRODUCT(COUNTIF(A1:A100,{"P";"HP"}),{1;0.5})

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom



"ryanjh79" wrote:

I would like to use the countif function to count a letter a a half ".5"
instead of as a whole "1." The application that it would be used for is
on a
payroll workbook. i.e. "P" would eaqul a whole personal day whereas a
"HP"
would be a half of a personal day. From there I would like to add all
of the
"P's" and "HP's" in one Cell. this would be like 4.5 days used compared
with
4 days used (P,P,P,P, HP)=4.5
Thanks,

Brandon

How about this way Peo, without the additional function:

=SUMPRODUCT(--(A1:A100={"P","HP"})*({1,0.5}))
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


"Peo Sjoblom" wrote in message
...
One way

=SUMPRODUCT(COUNTIF(A1:A100,{"P";"HP"}),{1;0.5})

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom



"ryanjh79" wrote:

I would like to use the countif function to count a letter a a half ".5"
instead of as a whole "1." The application that it would be used for is on
a
payroll workbook. i.e. "P" would eaqul a whole personal day whereas a
"HP"
would be a half of a personal day. From there I would like to add all of
the
"P's" and "HP's" in one Cell. this would be like 4.5 days used compared
with
4 days used (P,P,P,P, HP)=4.5
Thanks,

Brandon

True, although it will not work in this scenario

=SUMPRODUCT(--(A1:H100={"P","HP"})*({1,0.5}))

will return error while this will work

=SUMPRODUCT(COUNTIF(A1:H100,{"P";"HP"}),{1;0.5})

so it is a bit more robust

I believe I picked up this formula from Daniel M

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"RagDyer" wrote in message
...
How about this way Peo, without the additional function:

=SUMPRODUCT(--(A1:A100={"P","HP"})*({1,0.5}))
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


"Peo Sjoblom" wrote in message
...
One way

=SUMPRODUCT(COUNTIF(A1:A100,{"P";"HP"}),{1;0.5})

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom



"ryanjh79" wrote:

I would like to use the countif function to count a letter a a half ".5"
instead of as a whole "1." The application that it would be used for is
on
a
payroll workbook. i.e. "P" would eaqul a whole personal day whereas a
"HP"
would be a half of a personal day. From there I would like to add all of
the
"P's" and "HP's" in one Cell. this would be like 4.5 days used compared
with
4 days used (P,P,P,P, HP)=4.5
Thanks,

Brandon

Topic of conversation:

What made you use semicolons in the array constant?
Usually they separate rows, as opposed to columns.
It seems Countif() accepts either [ , ] or [ ; ],
As does an "OR",
=IF(OR(A1={"P";"HP"}),"OK","NG")
While regular
=SUMPRODUCT((A1:A100={"P","HP"})*{1,0.5})
Does not!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------



"Peo Sjoblom" wrote in message
...
True, although it will not work in this scenario

=SUMPRODUCT(--(A1:H100={"P","HP"})*({1,0.5}))

will return error while this will work

=SUMPRODUCT(COUNTIF(A1:H100,{"P";"HP"}),{1;0.5})

so it is a bit more robust

I believe I picked up this formula from Daniel M

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"RagDyer" wrote in message
...
How about this way Peo, without the additional function:

=SUMPRODUCT(--(A1:A100={"P","HP"})*({1,0.5}))
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


"Peo Sjoblom" wrote in message
...
One way

=SUMPRODUCT(COUNTIF(A1:A100,{"P";"HP"}),{1;0.5})

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom



"ryanjh79" wrote:

I would like to use the countif function to count a letter a a half ".5"
instead of as a whole "1." The application that it would be used for is
on
a
payroll workbook. i.e. "P" would eaqul a whole personal day whereas a
"HP"
would be a half of a personal day. From there I would like to add all of
the
"P's" and "HP's" in one Cell. this would be like 4.5 days used compared
with
4 days used (P,P,P,P, HP)=4.5
Thanks,

Brandon

When I test a formula I usually put the values in a range, i.e.
A B
H 1
HP 0.5

then I use the range itself in the formula and finally I select the range in
the formula
bar and press F9 to hardcode it. Since most data are in rows in a column I
usually use that unless the OP notes in his/her post using data going across,
then it would be comma instead when I hard code my formula test


Regards,

Peo Sjoblom

"RagDyer" wrote:

Topic of conversation:

What made you use semicolons in the array constant?
Usually they separate rows, as opposed to columns.
It seems Countif() accepts either [ , ] or [ ; ],
As does an "OR",
=IF(OR(A1={"P";"HP"}),"OK","NG")
While regular
=SUMPRODUCT((A1:A100={"P","HP"})*{1,0.5})
Does not!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------



"Peo Sjoblom" wrote in message
...
True, although it will not work in this scenario

=SUMPRODUCT(--(A1:H100={"P","HP"})*({1,0.5}))

will return error while this will work

=SUMPRODUCT(COUNTIF(A1:H100,{"P";"HP"}),{1;0.5})

so it is a bit more robust

I believe I picked up this formula from Daniel M

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"RagDyer" wrote in message
...
How about this way Peo, without the additional function:

=SUMPRODUCT(--(A1:A100={"P","HP"})*({1,0.5}))
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


"Peo Sjoblom" wrote in message
...
One way

=SUMPRODUCT(COUNTIF(A1:A100,{"P";"HP"}),{1;0.5})

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom



"ryanjh79" wrote:

I would like to use the countif function to count a letter a a half ".5"
instead of as a whole "1." The application that it would be used for is
on
a
payroll workbook. i.e. "P" would eaqul a whole personal day whereas a
"HP"
would be a half of a personal day. From there I would like to add all of
the
"P's" and "HP's" in one Cell. this would be like 4.5 days used compared
with
4 days used (P,P,P,P, HP)=4.5
Thanks,

Brandon

You sound organized in your development procedures.
Not as haphazard as us folks who dye for a living, and just use this stuff
to get *OUR* job done.<bg

--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Peo Sjoblom" wrote in message
...
When I test a formula I usually put the values in a range, i.e.
A B
H 1
HP 0.5

then I use the range itself in the formula and finally I select the range in
the formula
bar and press F9 to hardcode it. Since most data are in rows in a column I
usually use that unless the OP notes in his/her post using data going
across,
then it would be comma instead when I hard code my formula test


Regards,

Peo Sjoblom

"RagDyer" wrote:

Topic of conversation:

What made you use semicolons in the array constant?
Usually they separate rows, as opposed to columns.
It seems Countif() accepts either [ , ] or [ ; ],
As does an "OR",
=IF(OR(A1={"P";"HP"}),"OK","NG")
While regular
=SUMPRODUCT((A1:A100={"P","HP"})*{1,0.5})
Does not!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------



"Peo Sjoblom" wrote in message
...
True, although it will not work in this scenario

=SUMPRODUCT(--(A1:H100={"P","HP"})*({1,0.5}))

will return error while this will work

=SUMPRODUCT(COUNTIF(A1:H100,{"P";"HP"}),{1;0.5})

so it is a bit more robust

I believe I picked up this formula from Daniel M

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"RagDyer" wrote in message
...
How about this way Peo, without the additional function:

=SUMPRODUCT(--(A1:A100={"P","HP"})*({1,0.5}))
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


"Peo Sjoblom" wrote in message
...
One way

=SUMPRODUCT(COUNTIF(A1:A100,{"P";"HP"}),{1;0.5})

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom



"ryanjh79" wrote:

I would like to use the countif function to count a letter a a half
".5"
instead of as a whole "1." The application that it would be used for is
on
a
payroll workbook. i.e. "P" would eaqul a whole personal day whereas a
"HP"
would be a half of a personal day. From there I would like to add all
of
the
"P's" and "HP's" in one Cell. this would be like 4.5 days used compared
with
4 days used (P,P,P,P, HP)=4.5
Thanks,

Brandon