using countif function to add only a half of a number

R

ryanjh79

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
 
P

Peo Sjoblom

One way

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

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom
 
B

Bob Phillips

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

Bob
 
R

RagDyer

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!
-------------------------------------------------------------------


One way

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

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom
 
P

Peo Sjoblom

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)
 
R

RagDyer

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!
-------------------------------------------------------------------



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)
 
P

Peo Sjoblom

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 said:
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!
-------------------------------------------------------------------



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 said:
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!
-------------------------------------------------------------------


One way

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

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom
Click to expand...
Click to expand...
 
R

RagDyer

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!
-------------------------------------------------------------------

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 said:
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!
-------------------------------------------------------------------



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 said:
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!
-------------------------------------------------------------------


One way

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

where your P/HP days are in A1:A100


Regards,

Peo Sjoblom



of
the
Click to expand...
Click to expand...
 
Top