# Formula is too long....

Closed
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010 - May 4, 2010 at 12:04 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 7, 2010 at 10:44 AM
Can anyone simplify this?

=IF(SUM(COUNTIF(M198,"Y"),COUNTIF(M200,"Y"),COUNTIF(M202,"Y"),COUNTIF(M204,"Y"),COUNTIF(M206,"Y"),COUNTIF(M208,"Y"),COUNTIF(M210,"Y"),COUNTIF(M212,"Y"),COUNTIF(M218,"Y"),COUNTIF(M220,"Y"),COUNTIF(M222,"Y"),COUNTIF(M224,"Y"),COUNTIF(M226,"Y"),COUNTIF(M228,"Y"),COUNTIF(M230,"Y"),COUNTIF(M232,"Y"),COUNTIF(M234,"Y"),COUNTIF(M236,"Y"),COUNTIF(M238,"Y"),COUNTIF(M240,"Y"),COUNTIF(M242,"Y"),COUNTIF(M244,"Y"),COUNTIF(M246,"Y"),COUNTIF(M248,"Y"),COUNTIF(M250,"Y"),COUNTIF(M252,"Y"),COUNTIF(M254,"Y"),COUNTIF
(M256,"Y"),COUNTIF(M258,"Y"),COUNTIF(M260,"Y"))=0,"",SUM(COUNTIF(M198,"Y"),
COUNTIF(M200,"Y"),COUNTIF(M202,"Y"),COUNTIF(M204,"Y"),COUNTIF(M206,"Y"),
COUNTIF(M208,"Y"),COUNTIF(M210,"Y"),COUNTIF(M212,"Y"),COUNTIF(M218,"Y"),
COUNTIF(M220,"Y"),COUNTIF(M222,"Y"),COUNTIF(M224,"Y"),COUNTIF(M226,"Y"),
COUNTIF(M228,"Y"),COUNTIF(M230,"Y"),COUNTIF(M232,"Y"),COUNTIF(M234,"Y"),
COUNTIF(M236,"Y"),COUNTIF(M238,"Y"),COUNTIF(M240,"Y"),COUNTIF(M242,"Y"),
COUNTIF(M244,"Y"),COUNTIF(M246,"Y"),COUNTIF(M248,"Y"),COUNTIF(M250,"Y"),
COUNTIF(M252,"Y"),COUNTIF(M254,"Y"),COUNTIF(M256,"Y"),COUNTIF(M258,"Y"),
COUNTIF(M260,"Y")))

Thanks.

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 4, 2010 at 12:39 PM
Whats on odd number of rows? y cant they be included ?
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010
May 7, 2010 at 09:50 AM
Thanks, I figured it out!
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 7, 2010 at 09:53 AM
Thats great. It would have been helpful for others if you had mentioned what was your solution.
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010
May 7, 2010 at 10:11 AM
I'm sorry.

In M328, I put:

=SUM(COUNTIF(M198,"Y"),COUNTIF(M200,"Y"),COUNTIF(M202,"Y"),COUNTIF(M204,"Y"),COUNTIF(M206,"Y"),COUNTIF(M208,"Y"),COUNTIF(M210,"Y"),COUNTIF(M212,"Y"),COUNTIF(M218,"Y"),COUNTIF(M220,"Y"),COUNTIF(M222,"Y"),COUNTIF(M224,"Y"),COUNTIF(M226,"Y"),COUNTIF(M228,"Y"),COUNTIF(M230,"Y"),COUNTIF(M232,"Y"),COUNTIF(M234,"Y"),COUNTIF(M236,"Y"),COUNTIF(M238,"Y"),COUNTIF(M240,"Y"),COUNTIF(M242,"Y"),COUNTIF(M244,"Y"),COUNTIF(M246,"Y"),COUNTIF(M248,"Y"),COUNTIF(M250,"Y"),COUNTIF(M252,"Y"),COUNTIF(M254,"Y"),COUNTIF(M256,"Y"),COUNTIF(M258,"Y"),COUNTIF(M260,"Y"))

In M299, where I needed the formula that was TOO LARGE was this:

=IF(M328=0,"",SUM(COUNTIF(M198,"Y"),COUNTIF(M200,"Y"),COUNTIF(M202,"Y"),COUNTIF(M204,"Y"),COUNTIF(M206,"Y"),COUNTIF(M208,"Y"),COUNTIF(M210,"Y"),COUNTIF(M212,"Y"),COUNTIF(M218,"Y"),COUNTIF(M220,"Y"),COUNTIF(M222,"Y"),COUNTIF(M224,"Y"),COUNTIF(M226,"Y"),COUNTIF(M228,"Y"),COUNTIF(M230,"Y"),COUNTIF(M232,"Y"),COUNTIF(M234,"Y"),COUNTIF(M236,"Y"),COUNTIF(M238,"Y"),COUNTIF(M240,"Y"),COUNTIF(M242,"Y"),COUNTIF(M244,"Y"),COUNTIF(M246,"Y"),COUNTIF(M248,"Y"),COUNTIF(M250,"Y"),COUNTIF(M252,"Y"),COUNTIF(M254,"Y"),COUNTIF(M256,"Y"),COUNTIF(M258,"Y"),COUNTIF(M260,"Y")))

Basically, I cut it into half between 2 cells, them combined that data.

Thanks.
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 7, 2010 at 10:44 AM
Thanks for that

If you want you can use this array formula

=IF(SUMPRODUCT( IF(M198:M260="y",1,0) * IF(MOD(ROW(M198:M260),2)=0,1,0))-IF(M214="y",1,0) -IF(M216="y",1,0)=0, "",SUMPRODUCT( IF(M198:M260="y",1,0) * IF(MOD(ROW(M198:M260),2)=0,1,0))-IF(M214="y",1,0) -IF(M216="y",1,0))

To enter array formula, press CTRL + SHIFT + ENTER at same time. If you have done it correctly, it should enclose the formula between { }

This formula is based of even rows between 198, 260 and excluded 214 and 216