Desperately need help

[bryan.delara]

I have the following; -
3 sheet workbook, Sheet 1 called Front Page. Sheet 2 called Home and
sheet 3 called A.
Sheet 1 (Front Page) is just a sheet with instructions. On sheet 2
(Home) is the results of entries made on Sheet 3 (A).
I make the following entries on Sheet 2. A6 to A222 are names which
transfer onto Sheet 3 automatically using =A6 etc going across the
sheet from E7 to IT7.
Sheet 1 B2 to B222 is have a hyperlink to jump to a name on sheet 3
which is
=HYPERLINK("#A!R6C"&MATCH(A6,A!$D$7:$IS$7,0)+3,"jump")
On sheet 3 (A) I enter either of the following:- d or hd for holidays
the result going into E1, =SUMPRODUCT(--(E7:E2038="d"))--(--SUMPRODUCT
(--(E7:E2038="HD"))*0.5)
L for lateness going into cell E2, =SUMPRODUCT(--(E7:E2038="L"))
1 for absence going into cell E3, =SUMPRODUCT(--(E7:E2037=1),--
(E8:E2038<>1),--($A7:$A2037>=TODAY()-365)) which counts each 1 and
blocks of 1.
Au or AD going into cell E4, for full day or half day authorised,
=SUMPRODUCT(--(E7:E2038="AU"))--(--SUMPRODUCT(--(E7:E2038="AD"))*0.5)
All this works great but, as with all good things someone has come up
with something new to add.
Along with the 1 I now need to enter either 1 p or 1 up. The result of
1 still needs to do the same as I have now. The result of p needs to
go into E5, the result of up into E6.
I have been told this can be done using a macro and entering something
like 1,p.
Can anyone give me any help with this please. I am at a loss.

Bryan.

 

[Shane Devenshire]

Hi,

What exactly do you mean by "The result of p needs to
go into E5, the result of up into E6." How about in E5 type p or in E6 type
up? or in E5 enter =some cell where p is and E6 enter = some cell where up
is?

Thanks,
Shane Devenshire

 

[bryan.delara]

Hi,

What exactly do you mean by "The result of p needs to
go into E5, the result of up into E6."  How about in E5 type p or in E6type
up? or in E5 enter =some cell where p is and E6 enter = some cell where up
is?  

Thanks,
Shane Devenshire






- Show quoted text -

Thanks for answering Shane, I knew I'd mess it up what I was trying to
say.
In E5 I need the count of P's in that column in E5. The UP in E6.
Each row going down from A8 to A2037 has the date up to 2015. So I
need a count of either the P or up, but I also have to put a 1 in the
same cell. I count the 1's okay, but I can't figure how to count the P
etc at the same time. At the moment if I enter 1, p neither
counts. Have I explained it better?

Bryan.

 

[bryan.delara]

Thanks for answering Shane, I knew I'd mess it up what I was trying to
say.
In E5 I need the count of P's in that column in E5. The UP in E6.
Each row going down from A8 to A2037 has the date up to 2015. So I
need a count of either the P or up, but I also have to put a 1 in the
same cell. I count the 1's okay, but I can't figure how to count the P
etc at the same time. At the moment if I enter 1, p neither
counts. Have I explained it better?

Bryan.- Hide quoted text -

- Show quoted text -

I think now the easiest way to ask this, is this way.
I use this fornula to count 1's and blocks of 1's when I enter 1 into
cells. =SUMPRODUCT(--(E7:E2037=1),--(E8:E2038<>1),--($A7:$A2037>=TODAY
()-365)) This works fine. The result of the formula goes into cell E3.
Now I need to add a P or UP to the same cell I enter a 1 into. When I
enter 1 P or 1 UP that formula dosen't work. How can I isolate my
formula so that it does work, and add another formula so it counts the
P or UP but ignores the 1 so that my original formula can do its
thing. Not all cells will have the same entered.
I've tried many things with countif statements etc. I'm getting
nowhere fast. I would be greatful if anyone can point me in the right
direction.

Bryan.