Formula is too long....
Closed
DCecil07
Posts
30
Registration date
Monday 15 March 2010
Status
Member
Last seen
15 September 2010
-
4 May 2010 à 12:04
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 7 May 2010 à 10:44
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 7 May 2010 à 10:44
Related:
- Formula is too long....
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Excel color formula - Guide
- Credit summation formula - Guide
- Spreadsheet formula - Guide
- Formula to calculate vat in excel - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
4 May 2010 à 12:39
4 May 2010 à 12:39
Whats on odd number of rows? y cant they be included ?
7 May 2010 à 09:50
7 May 2010 à 09:53
7 May 2010 à 10:11
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.
7 May 2010 à 10:44
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