# Formula is too long....

Closed
Report

DCecil07

rizvisa1

- Posts
- 30
- Registration date
- Monday March 15, 2010
- Status
- Member
- Last seen
- September 15, 2010

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

Related:

- Formula is too long....
- Spreadsheet formula - Articles
- Excel @ in formula ✓ - Forum - Excel
- Little alchemy formulas - Guide
- 1st, 2nd, 3rd position formula in excel ✓ - Forum - Office Software
- How to copy data from one sheet to another in excel using formula - Guide

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.

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