Hi Mike,
Is this the original formula?
=IF(SUM(IF('Test Plan'!$I$13:$I$10134="NB",1,0))>0,SUM(IF('Test
Plan'!$I$13:$I$10134="NB",1,0)),0)+IF(SUM(IF('Test
Plan'!$I$13:$I$10134="QU",1,0))>0,SUM(IF(TestPlan!$I$13:$I$10134="QU",1,0)),0)+IF(SUM(IF('Test
Plan'!$I$13:$I$10134="SLIQ",1,0))>0,SUM(IF('Test
Plan'!$I$13:$I$10134="SLIQ",1,0)),0)+IF(SUM(IF('Test
Plan'!$I$13:$I$10134="PQB",1,0))>0,SUM(IF(TestPlan!$I$13:$I$10134="PQB",1,0)),0)-A3
It seems to be made up of 4 parts.
Each part is tested to see if it's result is greater than zero.
If yes, then sum the occurrances.
If no, then zero.
The four results are then added together.
Finally, A3 is subtracted from the total.
Is this about right?
Do you want to restrict the process to only those rows which have an '11' in
Column U?
And can I assume that the '14' in IF(AND('TEST PLAN'!$U$14:$U$10134="11" should
really be a '13'?
Dave.
> Date: Wed, 12 May 2010 14:49:29 -0700
> Subject: Re: $$Excel-Macros$$ IF AND formulas
> From: mb...@allstate.com
> To: excel-macros@googlegroups.com
>
> Hi Dave!
>
> I appreciate your taking the time to look at the formula I am having
> trouble with. I inherited this task and am trying to use what is
> already working. The formula is used in many different parts of a
> summary sheet, just different filter terms. What the existing
> formulas do, which by the way they are “Array formulas” is do an
> entire project which could last six months. What I was trying to do
> was captilize on what was working but filter it by month which uses a
> date calculation in column “U” and is why I added the following to the
> beginning of the formula: “IF(AND('TEST PLAN'!$U$14:$U$10134="11"=”.
> What if added doesn’t work and is what I need help with, I am not
> using the IF AND correctly.
>
> If I can use the existing formula with the correct addition of
> filtering on the date column which is actually in column “M” it will
> save me a good deal of time.
>
> I hope I have explained it clearly enough. I cannot send the file
> because it is propriety information.
>
> Thank you,
>
> MikeB
>
>
> On May 12, 5:17 am, Dave Bonallack <davebonall...@hotmail.com> wrote:
> > Hi,
> >
> > A sample file would be good, but to simplify your formula, use a a name for
> > 'Test Plan'!$I$13:$I$10134
> >
> > I've used XYZ and your formula now looks like this:
> >
> > =IF(AND(XYZ ="11",(IF(SUM(IF(XYZ ="NB",1,0)))>0,SUM(IF(XYZ
> > ="NB",1,0)),0)+IF(SUM(IF(XYZ ="QU",1,0))>0,SUM(IF(XYZ
> > ="QU",1,0)),0)+IF(SUM(IF(XYZ ="SLIQ",1,0))>0,SUM(IF(XYZ
> > ="SLIQ",1,0)),0)+IF(SUM(IF(XYZ ="PQB",1,0))>0,SUM(IF(XYZ
> > ="PQB",1,0)),0))))-A3
> >
> > But the whole thing looks very cumbersome, and discourages me from
> > diagnosing the error. Tell me, using words, what you want to achieve.
> > Perhaps there is a better way.
> >
> > Regards - Dave.
> >
> >
> >
> >
> >
> > > Date: Wed, 12 May 2010 13:00:44 +0530
> > > Subject: Re: $$Excel-Macros$$ IF AND formulas
> > > From: sandymau...@gmail.com
> > > To: excel-macros@googlegroups.com
> >
> > > Hi Mike,
> >
> > > It will be better if you send a sample file with some data and your
> > > query. As it may give you a shorter and simple formula for your task.
> >
> > > And about this formula, one can only show you the syntax error and
> > > that not guaranteed for formula to give desired result.
> >
> > > So if you can than attach a sample file.
> >
> > > Thanks & Regards
> > > Sandeep
> >
> > > On Wed, May 12, 2010 at 2:22 AM, mikeb <mb...@allstate.com> wrote:
> > > > I would appreciate any help on the following formula which I cannot
> > > > get to work:
> >
> > > > =IF(AND('TEST PLAN'!$U$14:$U$10134="11",(IF(SUM(IF('Test Plan'!$I$13:$I
> > > > $10134="NB",1,0)))>0,SUM(IF('Test Plan'!$I$13:$I$10134="NB",1,0)),
> > > > 0)+IF(SUM(IF('Test Plan'!$I$13:$I$10134="QU",1,0))>0,SUM(IF('Test
> > > > Plan'!$I$13:$I$10134="QU",1,0)),0)+IF(SUM(IF('Test Plan'!$I$13:$I
> > > > $10134="SLIQ",1,0))>0,SUM(IF('Test Plan'!$I$13:$I$10134="SLIQ",1,0)),
> > > > 0)+IF(SUM(IF('Test Plan'!$I$13:$I$10134="PQB",1,0))>0,SUM(IF('Test
> > > > Plan'!$I$13:$I$10134="PQB",1,0)),0))))-A3
> >
> > > > I am trying to qualify the formulas at the beginning by testing to see
> > > > if 'TEST PLAN'!$U$14:$U$10134="11" if it is true then I want to
> > > > proceed with the rest of the formulas.
> >
> > > > Thank you for any help.
> >
> > > > Mike B
> >
> > > > --
> > > > ----------------------------------------------------------------------------------
> > > > Some important links for excel users:
> > > > 1. Follow us on TWITTER for tips tricks and links
> > > > :http://twitter.com/exceldailytip
> > > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> >
> > > > To post to this group, send email to excel-macros@googlegroups.com
> >
> > > > <><><><><><><><><><><><><><><><><><><><><><>
> > > > HELP US GROW !!
> >
> > > > We reach over 6,800 subscribers worldwide and receive many nice notes
> > > > about the learning and support from the group.Let friends and
> > > > co-workers know they can subscribe to group
> > > > athttp://groups.google.com/group/excel-macros/subscribe
> >
> > > --
> > > ----------------------------------------------------------------------------------
> > > Some important links for excel users:
> > > 1. Follow us on TWITTER for tips tricks and links
> > > :http://twitter.com/exceldailytip
> > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> >
> > > To post to this group, send email to excel-macros@googlegroups.com
> >
> > > <><><><><><><><><><><><><><><><><><><><><><>
> > > HELP US GROW !!
> >
> > > We reach over 6,800 subscribers worldwide and receive many nice notes
> > > about the learning and support from the group.Let friends and co-workers
> > > know they can subscribe to group
> > > athttp://groups.google.com/group/excel-macros/subscribe
> >
> > _________________________________________________________________
> > Browse profiles for FREE! Meet local singles
> > online.http://clk.atdmt.com/NMN/go/150855801/direct/01/
> >
> > --
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links
> > :http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> >
> > To post to this group, send email to excel-macros@googlegroups.com
> >
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
> >
> > We reach over 6,800 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know
> > they can subscribe to group
> > athttp://groups.google.com/group/excel-macros/subscribe- Hide quoted text -
> >
> > - Show quoted text -
>
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,800 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
_________________________________________________________________
Browse profiles for FREE! Meet local singles online.
http://clk.atdmt.com/NMN/go/150855801/direct/01/
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
