A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do I shorten an IF formula?



 
 
Thread Tools Display Modes
  #1  
Old February 25th, 2009, 11:10 PM posted to microsoft.public.excel.worksheet.functions
fjm
external usenet poster
 
Posts: 5
Default How do I shorten an IF formula?

How do I shorten this formula. It is missing information from the missing
rows, but if I put those in, it tells me "The specified formula cannot be
entered because it contains more values, references and/or names than are
allowed in the current file format."

Here is the formula...
=IF($F70,IF($G7=0,J7),0)+IF($F80,IF($G8=0,J8),0) +IF($F90,IF($G9=0,J9),0)+IF($F100,IF($G10=0,J10) ,0)+IF($F110,IF($G11=0,J11),0)+IF($F120,IF($G12= 0,J12),0)+IF($F130,IF($G13=0,J13),0)+IF($F140,IF ($G$14=0,J14),0)+IF($F$150,IF($G$15=0,J15),0)+IF( $F$160,IF($G$16=0,J16),0)+IF($F$170,IF($G$17=0,J 17),0)+IF($F$180,IF($G$18=0,J18),0)+IF($F$190,IF ($G19=0,J19),0)+IF($F200,IF($G20=0,J20),0)+IF($F2 10,IF($G21=0,J21),0)+IF($F220,IF($G22=0,J22),0)+ IF($F230,IF($G23=0,J23),0)+IF($F240,IF($G24=0,J2 4),0)+IF($F250,IF($G25=0,J25),0)+IF($F260,IF($G2 6=0,J26),0)+IF($F270,IF($G27=0,J27),0)+IF($F$330 ,IF($G$33=0,J33),0)+IF($F$370,IF($G$37=0,J37),0)+ IF($F$380,IF($G$38=0,J38),0)+IF($F$390,IF($G$39= 0,J39),0)+IF($F$400,IF($G$40=0,J40),0)+IF($F$410 ,IF($G$41=0,J41),0)+IF($F$420,IF($G$42=0,J42),0)+ IF($F$430,IF($G$43=0,J43),0)+IF($F$440,IF($G$44= 0,J44),0)

Can anyone help me out, I hope?! Please?! Thank you!
  #2  
Old February 25th, 2009, 11:18 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default How do I shorten an IF formula?

Try this:

=SUMPRODUCT(--(F7:F440),--(G7:G44=0),J7:J44)

Note that if cells in the range G7:G44 are empty they will evaluate as being
equal to 0.

To account for that if needed:

=SUMPRODUCT(--(F7:F440),--(G7:G44""),--(G7:G44=0),J7:J44)

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...
How do I shorten this formula. It is missing information from the missing
rows, but if I put those in, it tells me "The specified formula cannot be
entered because it contains more values, references and/or names than are
allowed in the current file format."

Here is the formula...
=IF($F70,IF($G7=0,J7),0)+IF($F80,IF($G8=0,J8),0) +IF($F90,IF($G9=0,J9),0)+IF($F100,IF($G10=0,J10) ,0)+IF($F110,IF($G11=0,J11),0)+IF($F120,IF($G12= 0,J12),0)+IF($F130,IF($G13=0,J13),0)+IF($F140,IF ($G$14=0,J14),0)+IF($F$150,IF($G$15=0,J15),0)+IF( $F$160,IF($G$16=0,J16),0)+IF($F$170,IF($G$17=0,J 17),0)+IF($F$180,IF($G$18=0,J18),0)+IF($F$190,IF ($G19=0,J19),0)+IF($F200,IF($G20=0,J20),0)+IF($F2 10,IF($G21=0,J21),0)+IF($F220,IF($G22=0,J22),0)+ IF($F230,IF($G23=0,J23),0)+IF($F240,IF($G24=0,J2 4),0)+IF($F250,IF($G25=0,J25),0)+IF($F260,IF($G2 6=0,J26),0)+IF($F270,IF($G27=0,J27),0)+IF($F$330 ,IF($G$33=0,J33),0)+IF($F$370,IF($G$37=0,J37),0)+ IF($F$380,IF($G$38=0,J38),0)+IF($F$390,IF($G$39= 0,J39),0)+IF($F$400,IF($G$40=0,J40),0)+IF($F$410 ,IF($G$41=0,J41),0)+IF($F$420,IF($G$42=0,J42),0)+ IF($F$430,IF($G$43=0,J43),0)+IF($F$440,IF($G$44= 0,J44),0)

Can anyone help me out, I hope?! Please?! Thank you!



  #3  
Old February 25th, 2009, 11:37 PM posted to microsoft.public.excel.worksheet.functions
fjm
external usenet poster
 
Posts: 5
Default How do I shorten an IF formula?

Great, that worked for part of my cells, changing the formulas for the
specifics WONDERFUL!!! Thank you!

Now I have a formula that pulls from two different cells.

=IF($G$70,IF($F$70,J$7),0)+IF($G$80,IF($F$80,J $8),0)+IF($G$90,IF($F$90,J$9),0)+IF($G$100,IF($ F$100,J$10),0)+IF($G$110,IF($F$110,J$11),0)+IF( $G$120,IF($F$120,J$12),0)+IF($G$130,IF($F$130, J$13),0)+IF($G$140,IF($F$140,J$14),0)+IF($G$150 ,IF($F$150,J$15),0)+IF($G$160,IF($F$160,J$16),0 )+IF($G$170,IF($F$170,J$17),0)+IF($G$180,IF($F$ 180,J$18),0)+IF($G$190,IF($F230,J$19),0)+IF($G$ 200,IF($F$200,J$20),0)+IF($G$210,IF($F$210,J$2 1),0)+IF($G$220,IF($F$220,J$22),0)+IF($G$230,IF ($F$230,J$23),0)+IF($G$240,IF($F$240,J$24),0)+I F($G$250,IF($F$250,J$25),0)+IF($G$260,IF($F$26 0,J$26),0)+IF($G$270,IF($F$270,J$27),0)+IF($G$33 0,IF($F$330,J$33),0)+IF($G$370,IF($F$370,J$37) ,0)+IF($G$380,IF($F$380,J$38),0)+IF($G$390,IF($ F$390,J$39),0)+IF($G$400,IF($F$400,J$40),0)+IF( $G$410,IF($F$410,J$41),0)+IF($G$420,IF($F$420, J$42),0)+IF($G$430,IF($F$430,J$43),0)+IF($G$440 ,IF($F$440,J$44),0)

Can you help me make sense of this one?

Thanks again!
Franie

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(F7:F440),--(G7:G44=0),J7:J44)

Note that if cells in the range G7:G44 are empty they will evaluate as being
equal to 0.

To account for that if needed:

=SUMPRODUCT(--(F7:F440),--(G7:G44""),--(G7:G44=0),J7:J44)

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...
How do I shorten this formula. It is missing information from the missing
rows, but if I put those in, it tells me "The specified formula cannot be
entered because it contains more values, references and/or names than are
allowed in the current file format."

Here is the formula...
=IF($F70,IF($G7=0,J7),0)+IF($F80,IF($G8=0,J8),0) +IF($F90,IF($G9=0,J9),0)+IF($F100,IF($G10=0,J10) ,0)+IF($F110,IF($G11=0,J11),0)+IF($F120,IF($G12= 0,J12),0)+IF($F130,IF($G13=0,J13),0)+IF($F140,IF ($G$14=0,J14),0)+IF($F$150,IF($G$15=0,J15),0)+IF( $F$160,IF($G$16=0,J16),0)+IF($F$170,IF($G$17=0,J 17),0)+IF($F$180,IF($G$18=0,J18),0)+IF($F$190,IF ($G19=0,J19),0)+IF($F200,IF($G20=0,J20),0)+IF($F2 10,IF($G21=0,J21),0)+IF($F220,IF($G22=0,J22),0)+ IF($F230,IF($G23=0,J23),0)+IF($F240,IF($G24=0,J2 4),0)+IF($F250,IF($G25=0,J25),0)+IF($F260,IF($G2 6=0,J26),0)+IF($F270,IF($G27=0,J27),0)+IF($F$330 ,IF($G$33=0,J33),0)+IF($F$370,IF($G$37=0,J37),0)+ IF($F$380,IF($G$38=0,J38),0)+IF($F$390,IF($G$39= 0,J39),0)+IF($F$400,IF($G$40=0,J40),0)+IF($F$410 ,IF($G$41=0,J41),0)+IF($F$420,IF($G$42=0,J42),0)+ IF($F$430,IF($G$43=0,J43),0)+IF($F$440,IF($G$44= 0,J44),0)

Can anyone help me out, I hope?! Please?! Thank you!




  #4  
Old February 25th, 2009, 11:54 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default How do I shorten an IF formula?

Follow the same basic syntax but change the operator:

=SUMPRODUCT(--(F7:F440),--(G7:G440),J7:J44)

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...
Great, that worked for part of my cells, changing the formulas for the
specifics WONDERFUL!!! Thank you!

Now I have a formula that pulls from two different cells.

=IF($G$70,IF($F$70,J$7),0)+IF($G$80,IF($F$80,J $8),0)+IF($G$90,IF($F$90,J$9),0)+IF($G$100,IF($ F$100,J$10),0)+IF($G$110,IF($F$110,J$11),0)+IF( $G$120,IF($F$120,J$12),0)+IF($G$130,IF($F$130, J$13),0)+IF($G$140,IF($F$140,J$14),0)+IF($G$150 ,IF($F$150,J$15),0)+IF($G$160,IF($F$160,J$16),0 )+IF($G$170,IF($F$170,J$17),0)+IF($G$180,IF($F$ 180,J$18),0)+IF($G$190,IF($F230,J$19),0)+IF($G$ 200,IF($F$200,J$20),0)+IF($G$210,IF($F$210,J$2 1),0)+IF($G$220,IF($F$220,J$22),0)+IF($G$230,IF ($F$230,J$23),0)+IF($G$240,IF($F$240,J$24),0)+I F($G$250,IF($F$250,J$25),0)+IF($G$260,IF($F$26 0,J$26),0)+IF($G$270,IF($F$270,J$27),0)+IF($G$33 0,IF($F$330,J$33),0)+IF($G$370,IF($F$370,J$37) ,0)+IF($G$380,IF($F$380,J$38),0)+IF($G$390,IF($ F$390,J$39),0)+IF($G$400,IF($F$400,J$40),0)+IF( $G$410,IF($F$410,J$41),0)+IF($G$420,IF($F$420, J$42),0)+IF($G$430,IF($F$430,J$43),0)+IF($G$440 ,IF($F$440,J$44),0)

Can you help me make sense of this one?

Thanks again!
Franie

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(F7:F440),--(G7:G44=0),J7:J44)

Note that if cells in the range G7:G44 are empty they will evaluate as
being
equal to 0.

To account for that if needed:

=SUMPRODUCT(--(F7:F440),--(G7:G44""),--(G7:G44=0),J7:J44)

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...
How do I shorten this formula. It is missing information from the
missing
rows, but if I put those in, it tells me "The specified formula cannot
be
entered because it contains more values, references and/or names than
are
allowed in the current file format."

Here is the formula...
=IF($F70,IF($G7=0,J7),0)+IF($F80,IF($G8=0,J8),0) +IF($F90,IF($G9=0,J9),0)+IF($F100,IF($G10=0,J10) ,0)+IF($F110,IF($G11=0,J11),0)+IF($F120,IF($G12= 0,J12),0)+IF($F130,IF($G13=0,J13),0)+IF($F140,IF ($G$14=0,J14),0)+IF($F$150,IF($G$15=0,J15),0)+IF( $F$160,IF($G$16=0,J16),0)+IF($F$170,IF($G$17=0,J 17),0)+IF($F$180,IF($G$18=0,J18),0)+IF($F$190,IF ($G19=0,J19),0)+IF($F200,IF($G20=0,J20),0)+IF($F2 10,IF($G21=0,J21),0)+IF($F220,IF($G22=0,J22),0)+ IF($F230,IF($G23=0,J23),0)+IF($F240,IF($G24=0,J2 4),0)+IF($F250,IF($G25=0,J25),0)+IF($F260,IF($G2 6=0,J26),0)+IF($F270,IF($G27=0,J27),0)+IF($F$330 ,IF($G$33=0,J33),0)+IF($F$370,IF($G$37=0,J37),0)+ IF($F$380,IF($G$38=0,J38),0)+IF($F$390,IF($G$39= 0,J39),0)+IF($F$400,IF($G$40=0,J40),0)+IF($F$410 ,IF($G$41=0,J41),0)+IF($F$420,IF($G$42=0,J42),0)+ IF($F$430,IF($G$43=0,J43),0)+IF($F$440,IF($G$44= 0,J44),0)

Can anyone help me out, I hope?! Please?! Thank you!






  #5  
Old February 26th, 2009, 12:04 AM posted to microsoft.public.excel.worksheet.functions
fjm
external usenet poster
 
Posts: 5
Default How do I shorten an IF formula?

Ok, I copied the original formula, but I didn't change anything and it didn't
work for me. (I don't do these kinds of formulas every day
obviously...actually I'm helping a collegue out). The extent of my formulas
are COUNTA & SUM.
This formula DID work!
Thank you again! You've been MOST helpful!! We both appreciate your help!
Franie

"T. Valko" wrote:

Follow the same basic syntax but change the operator:

=SUMPRODUCT(--(F7:F440),--(G7:G440),J7:J44)

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...
Great, that worked for part of my cells, changing the formulas for the
specifics WONDERFUL!!! Thank you!

Now I have a formula that pulls from two different cells.

=IF($G$70,IF($F$70,J$7),0)+IF($G$80,IF($F$80,J $8),0)+IF($G$90,IF($F$90,J$9),0)+IF($G$100,IF($ F$100,J$10),0)+IF($G$110,IF($F$110,J$11),0)+IF( $G$120,IF($F$120,J$12),0)+IF($G$130,IF($F$130, J$13),0)+IF($G$140,IF($F$140,J$14),0)+IF($G$150 ,IF($F$150,J$15),0)+IF($G$160,IF($F$160,J$16),0 )+IF($G$170,IF($F$170,J$17),0)+IF($G$180,IF($F$ 180,J$18),0)+IF($G$190,IF($F230,J$19),0)+IF($G$ 200,IF($F$200,J$20),0)+IF($G$210,IF($F$210,J$2 1),0)+IF($G$220,IF($F$220,J$22),0)+IF($G$230,IF ($F$230,J$23),0)+IF($G$240,IF($F$240,J$24),0)+I F($G$250,IF($F$250,J$25),0)+IF($G$260,IF($F$26 0,J$26),0)+IF($G$270,IF($F$270,J$27),0)+IF($G$33 0,IF($F$330,J$33),0)+IF($G$370,IF($F$370,J$37) ,0)+IF($G$380,IF($F$380,J$38),0)+IF($G$390,IF($ F$390,J$39),0)+IF($G$400,IF($F$400,J$40),0)+IF( $G$410,IF($F$410,J$41),0)+IF($G$420,IF($F$420, J$42),0)+IF($G$430,IF($F$430,J$43),0)+IF($G$440 ,IF($F$440,J$44),0)

Can you help me make sense of this one?

Thanks again!
Franie

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(F7:F440),--(G7:G44=0),J7:J44)

Note that if cells in the range G7:G44 are empty they will evaluate as
being
equal to 0.

To account for that if needed:

=SUMPRODUCT(--(F7:F440),--(G7:G44""),--(G7:G44=0),J7:J44)

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...
How do I shorten this formula. It is missing information from the
missing
rows, but if I put those in, it tells me "The specified formula cannot
be
entered because it contains more values, references and/or names than
are
allowed in the current file format."

Here is the formula...
=IF($F70,IF($G7=0,J7),0)+IF($F80,IF($G8=0,J8),0) +IF($F90,IF($G9=0,J9),0)+IF($F100,IF($G10=0,J10) ,0)+IF($F110,IF($G11=0,J11),0)+IF($F120,IF($G12= 0,J12),0)+IF($F130,IF($G13=0,J13),0)+IF($F140,IF ($G$14=0,J14),0)+IF($F$150,IF($G$15=0,J15),0)+IF( $F$160,IF($G$16=0,J16),0)+IF($F$170,IF($G$17=0,J 17),0)+IF($F$180,IF($G$18=0,J18),0)+IF($F$190,IF ($G19=0,J19),0)+IF($F200,IF($G20=0,J20),0)+IF($F2 10,IF($G21=0,J21),0)+IF($F220,IF($G22=0,J22),0)+ IF($F230,IF($G23=0,J23),0)+IF($F240,IF($G24=0,J2 4),0)+IF($F250,IF($G25=0,J25),0)+IF($F260,IF($G2 6=0,J26),0)+IF($F270,IF($G27=0,J27),0)+IF($F$330 ,IF($G$33=0,J33),0)+IF($F$370,IF($G$37=0,J37),0)+ IF($F$380,IF($G$38=0,J38),0)+IF($F$390,IF($G$39= 0,J39),0)+IF($F$400,IF($G$40=0,J40),0)+IF($F$410 ,IF($G$41=0,J41),0)+IF($F$420,IF($G$42=0,J42),0)+ IF($F$430,IF($G$43=0,J43),0)+IF($F$440,IF($G$44= 0,J44),0)

Can anyone help me out, I hope?! Please?! Thank you!






  #6  
Old February 26th, 2009, 12:23 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default How do I shorten an IF formula?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...
Ok, I copied the original formula, but I didn't change anything and it
didn't
work for me. (I don't do these kinds of formulas every day
obviously...actually I'm helping a collegue out). The extent of my
formulas
are COUNTA & SUM.
This formula DID work!
Thank you again! You've been MOST helpful!! We both appreciate your
help!
Franie

"T. Valko" wrote:

Follow the same basic syntax but change the operator:

=SUMPRODUCT(--(F7:F440),--(G7:G440),J7:J44)

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...
Great, that worked for part of my cells, changing the formulas for the
specifics WONDERFUL!!! Thank you!

Now I have a formula that pulls from two different cells.

=IF($G$70,IF($F$70,J$7),0)+IF($G$80,IF($F$80,J $8),0)+IF($G$90,IF($F$90,J$9),0)+IF($G$100,IF($ F$100,J$10),0)+IF($G$110,IF($F$110,J$11),0)+IF( $G$120,IF($F$120,J$12),0)+IF($G$130,IF($F$130, J$13),0)+IF($G$140,IF($F$140,J$14),0)+IF($G$150 ,IF($F$150,J$15),0)+IF($G$160,IF($F$160,J$16),0 )+IF($G$170,IF($F$170,J$17),0)+IF($G$180,IF($F$ 180,J$18),0)+IF($G$190,IF($F230,J$19),0)+IF($G$ 200,IF($F$200,J$20),0)+IF($G$210,IF($F$210,J$2 1),0)+IF($G$220,IF($F$220,J$22),0)+IF($G$230,IF ($F$230,J$23),0)+IF($G$240,IF($F$240,J$24),0)+I F($G$250,IF($F$250,J$25),0)+IF($G$260,IF($F$26 0,J$26),0)+IF($G$270,IF($F$270,J$27),0)+IF($G$33 0,IF($F$330,J$33),0)+IF($G$370,IF($F$370,J$37) ,0)+IF($G$380,IF($F$380,J$38),0)+IF($G$390,IF($ F$390,J$39),0)+IF($G$400,IF($F$400,J$40),0)+IF( $G$410,IF($F$410,J$41),0)+IF($G$420,IF($F$420, J$42),0)+IF($G$430,IF($F$430,J$43),0)+IF($G$440 ,IF($F$440,J$44),0)

Can you help me make sense of this one?

Thanks again!
Franie

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(F7:F440),--(G7:G44=0),J7:J44)

Note that if cells in the range G7:G44 are empty they will evaluate as
being
equal to 0.

To account for that if needed:

=SUMPRODUCT(--(F7:F440),--(G7:G44""),--(G7:G44=0),J7:J44)

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...
How do I shorten this formula. It is missing information from the
missing
rows, but if I put those in, it tells me "The specified formula
cannot
be
entered because it contains more values, references and/or names
than
are
allowed in the current file format."

Here is the formula...
=IF($F70,IF($G7=0,J7),0)+IF($F80,IF($G8=0,J8),0) +IF($F90,IF($G9=0,J9),0)+IF($F100,IF($G10=0,J10) ,0)+IF($F110,IF($G11=0,J11),0)+IF($F120,IF($G12= 0,J12),0)+IF($F130,IF($G13=0,J13),0)+IF($F140,IF ($G$14=0,J14),0)+IF($F$150,IF($G$15=0,J15),0)+IF( $F$160,IF($G$16=0,J16),0)+IF($F$170,IF($G$17=0,J 17),0)+IF($F$180,IF($G$18=0,J18),0)+IF($F$190,IF ($G19=0,J19),0)+IF($F200,IF($G20=0,J20),0)+IF($F2 10,IF($G21=0,J21),0)+IF($F220,IF($G22=0,J22),0)+ IF($F230,IF($G23=0,J23),0)+IF($F240,IF($G24=0,J2 4),0)+IF($F250,IF($G25=0,J25),0)+IF($F260,IF($G2 6=0,J26),0)+IF($F270,IF($G27=0,J27),0)+IF($F$330 ,IF($G$33=0,J33),0)+IF($F$370,IF($G$37=0,J37),0)+ IF($F$380,IF($G$38=0,J38),0)+IF($F$390,IF($G$39= 0,J39),0)+IF($F$400,IF($G$40=0,J40),0)+IF($F$410 ,IF($G$41=0,J41),0)+IF($F$420,IF($G$42=0,J42),0)+ IF($F$430,IF($G$43=0,J43),0)+IF($F$440,IF($G$44= 0,J44),0)

Can anyone help me out, I hope?! Please?! Thank you!








  #7  
Old May 28th, 2010, 05:44 PM posted to microsoft.public.excel.worksheet.functions
Chris
external usenet poster
 
Posts: 29
Default Excel

I am getting the following error when trying to convert a 2007 worksheet to a 97-2003 format, "specified fmula cannot be entered because it contains me values". I have 19 different values in an IF formula, what can I do to adjust the formula to work?



T. Valko wrote:

You're welcome. Thanks for the feedback!
25-Feb-09

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Previous Posts In This Thread:

On Wednesday, February 25, 2009 5:10 PM
FJ wrote:

How do I shorten an IF formula?
How do I shorten this formula. It is missing information from the missing
rows, but if I put those in, it tells me "The specified formula cannot be
entered because it contains more values, references and/or names than are
allowed in the current file format."

Here is the formula...
=IF($F70,IF($G7=0,J7),0)+IF($F80,IF($G8=0,J8),0) +IF($F90,IF($G9=0,J9),0)+IF($F100,IF($G10=0,J10) ,0)+IF($F110,IF($G11=0,J11),0)+IF($F120,IF($G12= 0,J12),0)+IF($F130,IF($G13=0,J13),0)+IF($F140,IF ($G$14=0,J14),0)+IF($F$150,IF($G$15=0,J15),0)+IF( $F$160,IF($G$16=0,J16),0)+IF($F$170,IF($G$17=0,J 17),0)+IF($F$180,IF($G$18=0,J18),0)+IF($F$190,IF ($G19=0,J19),0)+IF($F200,IF($G20=0,J20),0)+IF($F2 10,IF($G21=0,J21),0)+IF($F220,IF($G22=0,J22),0)+ IF($F230,IF($G23=0,J23),0)+IF($F240,IF($G24=0,J2 4),0)+IF($F250,IF($G25=0,J25),0)+IF($F260,IF($G2 6=0,J26),0)+IF($F270,IF($G27=0,J27),0)+IF($F$330 ,IF($G$33=0,J33),0)+IF($F$370,IF($G$37=0,J37),0)+ IF($F$380,IF($G$38=0,J38),0)+IF($F$390,IF($G$39= 0,J39),0)+IF($F$400,IF($G$40=0,J40),0)+IF($F$410 ,IF($G$41=0,J41),0)+IF($F$420,IF($G$42=0,J42),0)+ IF($F$430,IF($G$43=0,J43),0)+IF($F$440,IF($G$44= 0,J44),0)

Can anyone help me out, I hope?! Please?! Thank you!

On Wednesday, February 25, 2009 5:18 PM
T. Valko wrote:

How do I shorten an IF formula?
Try this:

=SUMPRODUCT(--(F7:F440),--(G7:G44=0),J7:J44)

Note that if cells in the range G7:G44 are empty they will evaluate as being
equal to 0.

To account for that if needed:

=SUMPRODUCT(--(F7:F440),--(G7:G44""),--(G7:G44=0),J7:J44)

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...

On Wednesday, February 25, 2009 5:37 PM
FJ wrote:

Great, that worked for part of my cells, changing the formulas for the
Great, that worked for part of my cells, changing the formulas for the
specifics WONDERFUL!!! Thank you!

Now I have a formula that pulls from two different cells.

=IF($G$70,IF($F$70,J$7),0)+IF($G$80,IF($F$80,J $8),0)+IF($G$90,IF($F$90,J$9),0)+IF($G$100,IF($ F$100,J$10),0)+IF($G$110,IF($F$110,J$11),0)+IF( $G$120,IF($F$120,J$12),0)+IF($G$130,IF($F$130, J$13),0)+IF($G$140,IF($F$140,J$14),0)+IF($G$150 ,IF($F$150,J$15),0)+IF($G$160,IF($F$160,J$16),0 )+IF($G$170,IF($F$170,J$17),0)+IF($G$180,IF($F$ 180,J$18),0)+IF($G$190,IF($F230,J$19),0)+IF($G$ 200,IF($F$200,J$20),0)+IF($G$210,IF($F$210,J$2 1),0)+IF($G$220,IF($F$220,J$22),0)+IF($G$230,IF ($F$230,J$23),0)+IF($G$240,IF($F$240,J$24),0)+I F($G$250,IF($F$250,J$25),0)+IF($G$260,IF($F$26 0,J$26),0)+IF($G$270,IF($F$270,J$27),0)+IF($G$33 0,IF($F$330,J$33),0)+IF($G$370,IF($F$370,J$37) ,0)+IF($G$380,IF($F$380,J$38),0)+IF($G$390,IF($ F$390,J$39),0)+IF($G$400,IF($F$400,J$40),0)+IF( $G$410,IF($F$410,J$41),0)+IF($G$420,IF($F$420, J$42),0)+IF($G$430,IF($F$430,J$43),0)+IF($G$440 ,IF($F$440,J$44),0)

Can you help me make sense of this one?

Thanks again!
Franie

"T. Valko" wrote:

On Wednesday, February 25, 2009 5:54 PM
T. Valko wrote:

Follow the same basic syntax but change the
Follow the same basic syntax but change the operator:

=SUMPRODUCT(--(F7:F440),--(G7:G440),J7:J44)

--
Biff
Microsoft Excel MVP

On Wednesday, February 25, 2009 6:04 PM
FJ wrote:

Ok, I copied the original formula, but I didn't change anything and it didn't
Ok, I copied the original formula, but I didn't change anything and it didn't
work for me. (I don't do these kinds of formulas every day
obviously...actually I'm helping a collegue out). The extent of my formulas
are COUNTA & SUM.
This formula DID work!
Thank you again! You've been MOST helpful!! We both appreciate your help!
Franie

"T. Valko" wrote:

On Wednesday, February 25, 2009 6:23 PM
T. Valko wrote:

You're welcome. Thanks for the feedback!
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Report Engine, Part 4
http://www.eggheadcafe.com/tutorials...ne-part-4.aspx
  #8  
Old May 28th, 2010, 06:01 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Excel

97-2003 versions will accept only 7 nested IF's

Try some other formula like the one Biff posted


Gord Dibben MS Excel MVP

On Fri, 28 May 2010 09:44:18 -0700, Chris wrote:

I am getting the following error when trying to convert a 2007 worksheet to a 97-2003 format, "specified fmula cannot be entered because it contains me values". I have 19 different values in an IF formula, what can I do to adjust the formula to work?



T. Valko wrote:

You're welcome. Thanks for the feedback!
25-Feb-09

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Previous Posts In This Thread:

On Wednesday, February 25, 2009 5:10 PM
FJ wrote:

How do I shorten an IF formula?
How do I shorten this formula. It is missing information from the missing
rows, but if I put those in, it tells me "The specified formula cannot be
entered because it contains more values, references and/or names than are
allowed in the current file format."

Here is the formula...
=IF($F70,IF($G7=0,J7),0)+IF($F80,IF($G8=0,J8),0 )+IF($F90,IF($G9=0,J9),0)+IF($F100,IF($G10=0,J10 ),0)+IF($F110,IF($G11=0,J11),0)+IF($F120,IF($G12 =0,J12),0)+IF($F130,IF($G13=0,J13),0)+IF($F140,I F($G$14=0,J14),0)+IF($F$150,IF($G$15=0,J15),0)+IF ($F$160,IF($G$16=0,J16),0)+IF($F$170,IF($G$17=0, J17),0)+IF($F$180,IF($G$18=0,J18),0)+IF($F$190,I F($G19=0,J19),0)+IF($F200,IF($G20=0,J20),0)+IF($F 210,IF($G21=0,J21),0)+IF($F220,IF($G22=0,J22),0) +IF($F230,IF($G23=0,J23),0)+IF($F240,IF($G24=0,J 24),0)+IF($F250,IF($G25=0,J25),0)+IF($F260,IF($G 26=0,J26),0)+IF($F270,IF($G27=0,J27),0)+IF($F$33 0,IF($G$33=0,J33),0)+IF($F$370,IF($G$37=0,J37),0) +IF($F$380,IF($G$38=0,J38),0)+IF($F$390,IF($G$39 =0,J39),0)+IF($F$400,IF($G$40=0,J40),0)+IF($F$41 0,IF($G$41=0,J41),0)+IF($F$420,IF($G$42=0,J42),0) +IF($F$430,IF($G$43=0,J43),0)+IF($F$440,IF($G$44 =0,J44),0)

Can anyone help me out, I hope?! Please?! Thank you!

On Wednesday, February 25, 2009 5:18 PM
T. Valko wrote:

How do I shorten an IF formula?
Try this:

=SUMPRODUCT(--(F7:F440),--(G7:G44=0),J7:J44)

Note that if cells in the range G7:G44 are empty they will evaluate as being
equal to 0.

To account for that if needed:

=SUMPRODUCT(--(F7:F440),--(G7:G44""),--(G7:G44=0),J7:J44)

--
Biff
Microsoft Excel MVP


"FJM" wrote in message
...

On Wednesday, February 25, 2009 5:37 PM
FJ wrote:

Great, that worked for part of my cells, changing the formulas for the
Great, that worked for part of my cells, changing the formulas for the
specifics WONDERFUL!!! Thank you!

Now I have a formula that pulls from two different cells.

=IF($G$70,IF($F$70,J$7),0)+IF($G$80,IF($F$80, J$8),0)+IF($G$90,IF($F$90,J$9),0)+IF($G$100,IF( $F$100,J$10),0)+IF($G$110,IF($F$110,J$11),0)+IF ($G$120,IF($F$120,J$12),0)+IF($G$130,IF($F$130 ,J$13),0)+IF($G$140,IF($F$140,J$14),0)+IF($G$15 0,IF($F$150,J$15),0)+IF($G$160,IF($F$160,J$16), 0)+IF($G$170,IF($F$170,J$17),0)+IF($G$180,IF($F $180,J$18),0)+IF($G$190,IF($F230,J$19),0)+IF($G $200,IF($F$200,J$20),0)+IF($G$210,IF($F$210,J$ 21),0)+IF($G$220,IF($F$220,J$22),0)+IF($G$230,I F($F$230,J$23),0)+IF($G$240,IF($F$240,J$24),0)+ IF($G$250,IF($F$250,J$25),0)+IF($G$260,IF($F$26 0,J$26),0)+IF($G$270,IF($F$270,J$27),0)+IF($G$3 30,IF($F$330,J$33),0)+IF($G$370,IF($F$370,J$37 ),0)+IF($G$380,IF($F$380,J$38),0)+IF($G$390,IF( $F$390,J$39),0)+IF($G$400,IF($F$400,J$40),0)+IF ($G$410,IF($F$410,J$41),0)+IF($G$420,IF($F$420 ,J$42),0)+IF($G$430,IF($F$430,J$43),0)+IF($G$44 0,IF($F$440,J$44),0)

Can you help me make sense of this one?

Thanks again!
Franie

"T. Valko" wrote:

On Wednesday, February 25, 2009 5:54 PM
T. Valko wrote:

Follow the same basic syntax but change the
Follow the same basic syntax but change the operator:

=SUMPRODUCT(--(F7:F440),--(G7:G440),J7:J44)

--
Biff
Microsoft Excel MVP

On Wednesday, February 25, 2009 6:04 PM
FJ wrote:

Ok, I copied the original formula, but I didn't change anything and it didn't
Ok, I copied the original formula, but I didn't change anything and it didn't
work for me. (I don't do these kinds of formulas every day
obviously...actually I'm helping a collegue out). The extent of my formulas
are COUNTA & SUM.
This formula DID work!
Thank you again! You've been MOST helpful!! We both appreciate your help!
Franie

"T. Valko" wrote:

On Wednesday, February 25, 2009 6:23 PM
T. Valko wrote:

You're welcome. Thanks for the feedback!
You're welcome. Thanks for the feedback!


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.