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 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 7, 2010 at 10:44 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 7, 2010 at 10:44 AM
Related:
- Formula is too long....
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Credit summation formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 4, 2010 at 12:39 PM
May 4, 2010 at 12:39 PM
Whats on odd number of rows? y cant they be included ?
May 7, 2010 at 09:50 AM
May 7, 2010 at 09:53 AM
May 7, 2010 at 10:11 AM
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.
May 7, 2010 at 10:44 AM
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