software

Hi Everyone,

I am having really bad trouble with an excel spreadsheet made for one
of our guys to monitor the sales in our company (selling phones).

Basically we have a workbook that has all stuff like Pass, Fail, and
Pendings etc for each sales person split up by days. Then we have
formula at the end which adds up a summary of the weeks figures. The
only thing,the guy puts in H (for holiday) or S (for sickness) in the
cells if the sales person is absent, but this then screws up the
formula and we cant see the totals! We need something that will add up
the relevant cells but that counts a quot;Hquot; or quot;Squot; as a quot;0quot; if it is
present in the cell.

The existing formula is just

=SUM(E3 I3 M3 Q3 U3 Y3)

obviously in this there is nothing to say count H or S as zero.

Another person has suggested something like the formula below, however
we can't get this to work either.

=IF(OR(E3,I3,M3,Q3,U3,Y3=quot;Hquot;),0,IF(OR(E3,I3,M3,Q3, U3,Y3=quot;Squot;),0,quot;quot;)) (E3 I3 M3 Q3 U3 Y3)If anybody can help us out we would be eternally gratefull. I can't
stress how much this would please us if somebody knew the answer or
correct formula for this problem.

I have uploaded the actual form itself if anyone wants to take a look
at it.
It is here

s65.yousendit.com/d.aspx?id=1...M230PKIKYDLSK3

Thanks for taking your time to read this and i really appreciate any
help.

-Andy--
andrewsnaith
------------------------------------------------------------------------
andrewsnaith's Profile: www.excelforum.com/member.php...oamp;userid=32110
View this thread: www.excelforum.com/showthread...hreadid=518673hi andrew:

a klugy solution would be to use SUMIF for each day, ie

=SUMIF(B3,quot;gt;0quot;,B3) SUMIF(F3,quot;gt;0quot;,F3) SUMIF(J3,quot;gt;0quot; ,J3) SUMIF(N3,quot;gt;0quot;,N3) SUMIF(R3,quot;gt;0quot;,R3) SUMIF(V3, quot;gt;0quot;,V3)

hope that helps.

cheers. chili.

quot;andrewsnaithquot; wrote:

gt;
gt; Hi Everyone,
gt;
gt; I am having really bad trouble with an excel spreadsheet made for one
gt; of our guys to monitor the sales in our company (selling phones).
gt;
gt; Basically we have a workbook that has all stuff like Pass, Fail, and
gt; Pendings etc for each sales person split up by days. Then we have
gt; formula at the end which adds up a summary of the weeks figures. The
gt; only thing,the guy puts in H (for holiday) or S (for sickness) in the
gt; cells if the sales person is absent, but this then screws up the
gt; formula and we cant see the totals! We need something that will add up
gt; the relevant cells but that counts a quot;Hquot; or quot;Squot; as a quot;0quot; if it is
gt; present in the cell.
gt;
gt; The existing formula is just
gt;
gt; =SUM(E3 I3 M3 Q3 U3 Y3)
gt;
gt; obviously in this there is nothing to say count H or S as zero.
gt;
gt; Another person has suggested something like the formula below, however
gt; we can't get this to work either.
gt;
gt; =IF(OR(E3,I3,M3,Q3,U3,Y3=quot;Hquot;),0,IF(OR(E3,I3,M3,Q3, U3,Y3=quot;Squot;),0,quot;quot;)) (E3 I3 M3 Q3 U3 Y3)
gt;
gt;
gt; If anybody can help us out we would be eternally gratefull. I can't
gt; stress how much this would please us if somebody knew the answer or
gt; correct formula for this problem.
gt;
gt; I have uploaded the actual form itself if anyone wants to take a look
gt; at it.
gt; It is here
gt;
gt; s65.yousendit.com/d.aspx?id=1...M230PKIKYDLSK3
gt;
gt; Thanks for taking your time to read this and i really appreciate any
gt; help.
gt;
gt; -Andy
gt;
gt;
gt; --
gt; andrewsnaith
gt; ------------------------------------------------------------------------
gt; andrewsnaith's Profile: www.excelforum.com/member.php...oamp;userid=32110
gt; View this thread: www.excelforum.com/showthread...hreadid=518673
gt;
gt;


Try =SUBTOTAL(9,E3,I3,M3,Q3,U3,Y3)

9 refers to the SUM function and the next 6 arguments are the cells
that
you want to total. This function ignores text and just adds the
numbers.--
mphell0
------------------------------------------------------------------------
mphell0's Profile: www.excelforum.com/member.php...oamp;userid=30153
View this thread: www.excelforum.com/showthread...hreadid=518673Another way:

=SUM(IF((E3gt;0)*(I3gt;0)*(M3gt;0)*(Q3gt;0)*(U3gt;0)*(Y3gt;0), (E3,I3,M3,Q3,U3,Y3)))
this is an array formula so enter it using Ctrl Shift Enter, if done
correctly excel will put curly braces {} at each end of the formula.

HTH

Jean-Guy

quot;andrewsnaithquot; wrote:

gt;
gt; Hi Everyone,
gt;
gt; I am having really bad trouble with an excel spreadsheet made for one
gt; of our guys to monitor the sales in our company (selling phones).
gt;
gt; Basically we have a workbook that has all stuff like Pass, Fail, and
gt; Pendings etc for each sales person split up by days. Then we have
gt; formula at the end which adds up a summary of the weeks figures. The
gt; only thing,the guy puts in H (for holiday) or S (for sickness) in the
gt; cells if the sales person is absent, but this then screws up the
gt; formula and we cant see the totals! We need something that will add up
gt; the relevant cells but that counts a quot;Hquot; or quot;Squot; as a quot;0quot; if it is
gt; present in the cell.
gt;
gt; The existing formula is just
gt;
gt; =SUM(E3 I3 M3 Q3 U3 Y3)
gt;
gt; obviously in this there is nothing to say count H or S as zero.
gt;
gt; Another person has suggested something like the formula below, however
gt; we can't get this to work either.
gt;
gt; =IF(OR(E3,I3,M3,Q3,U3,Y3=quot;Hquot;),0,IF(OR(E3,I3,M3,Q3, U3,Y3=quot;Squot;),0,quot;quot;)) (E3 I3 M3 Q3 U3 Y3)
gt;
gt;
gt; If anybody can help us out we would be eternally gratefull. I can't
gt; stress how much this would please us if somebody knew the answer or
gt; correct formula for this problem.
gt;
gt; I have uploaded the actual form itself if anyone wants to take a look
gt; at it.
gt; It is here
gt;
gt; s65.yousendit.com/d.aspx?id=1...M230PKIKYDLSK3
gt;
gt; Thanks for taking your time to read this and i really appreciate any
gt; help.
gt;
gt; -Andy
gt;
gt;
gt; --
gt; andrewsnaith
gt; ------------------------------------------------------------------------
gt; andrewsnaith's Profile: www.excelforum.com/member.php...oamp;userid=32110
gt; View this thread: www.excelforum.com/showthread...hreadid=518673
gt;
gt;


Thanks ever so much everyone it has really helped me out. I went for
mphell's solutution and it seems to have worked, im ecstatic! I really
am thankfull to all of you who helped me out on this one, i cant
express how much of a pain this has been for me and im so glad to have
got it sorted before the weekend!

Once again many thanks, i really appreciate it.

Cheers

-Andy--
andrewsnaith
------------------------------------------------------------------------
andrewsnaith's Profile: www.excelforum.com/member.php...oamp;userid=32110
View this thread: www.excelforum.com/showthread...hreadid=518673