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.

1 reply

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 ?
1
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!
0
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.
0
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.
0
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
0