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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|