SOLVED

# how to inter formula in a cell has more 8192 characters

Copper Contributor

# how to inter formula in a cell has more 8192 characters

hello guys

I am using office 2019

I tried to maintain our work activity progress in the project by making a tracking plane depending on the entry data we have provided it in another sheet table

for an example:

I have a finished work along alignment from

station : 0+300 to 0+500

and from

station : 0+700 to 0+900

i would like highlight the cells under my station's dividing in the sheet

I have used this formula and it was successful:

=IF(OR(AND(A\$1>=BEDDING!\$A\$1,A\$1<=BEDDING!\$B\$1),AND(A\$1>=BEDDING!\$A\$2,A\$1<=BEDDING!\$B\$2),AND(A\$1>=BEDDING!\$A\$3,A\$1<=BEDDING!\$B\$3),AND(A\$1>=BEDDING!\$A\$4,A\$1<=BEDDING!\$B\$4),AND(A\$1>=BEDDING!\$A\$5,A\$1<=BEDDING!\$B\$5),AND(A\$1>=BEDDING!\$A\$6,A\$1<=BEDDING!\$B\$6),AND(A\$1>=BEDDING!\$A\$7,A\$1<=BEDDING!\$B\$7),AND(A\$1>=BEDDING!\$A\$8,A\$1<=BEDDING!\$B\$8),AND(A\$1>=BEDDING!\$A\$9,A\$1<=BEDDING!\$B\$9),AND(A\$1>=BEDDING!\$A\$10,A\$1<=BEDDING!\$B\$10),AND(A\$1>=BEDDING!\$A\$11,A\$1<=BEDDING!\$B\$11),AND(A\$1>=BEDDING!\$A\$12,A\$1<=BEDDING!\$B\$12),AND(A\$1>=BEDDING!\$A\$13,A\$1<=BEDDING!\$B\$13),AND(A\$1>=BEDDING!\$A\$14,A\$1<=BEDDING!\$B\$14),AND(A\$1>=BEDDING!\$A\$15,A\$1<=BEDDING!\$B\$15),AND(A\$1>=BEDDING!\$A\$16,A\$1<=BEDDING!\$B\$16),AND(A\$1>=BEDDING!\$A\$17,A\$1<=BEDDING!\$B\$17),AND(A\$1>=BEDDING!\$A\$18,A\$1<=BEDDING!\$B\$18),AND(A\$1>=BEDDING!\$A\$19,A\$1<=BEDDING!\$B\$19),AND(A\$1>=BEDDING!\$A\$20,A\$1<=BEDDING!\$B\$20),AND(A\$1>=BEDDING!\$A\$21,A\$1<=BEDDING!\$B\$21),AND(A\$1>=BEDDING!\$A\$22,A\$1<=BEDDING!\$B\$22),AND(A\$1>=BEDDING!\$A\$23,A\$1<=BEDDING!\$B\$23),AND(A\$1>=BEDDING!\$A\$24,A\$1<=BEDDING!\$B\$24),AND(A\$1>=BEDDING!\$A\$25,A\$1<=BEDDING!\$B\$25),AND(A\$1>=BEDDING!\$A\$26,A\$1<=BEDDING!\$B\$26),AND(A\$1>=BEDDING!\$A\$27,A\$1<=BEDDING!\$B\$27),AND(A\$1>=BEDDING!\$A\$28,A\$1<=BEDDING!\$B\$28),AND(A\$1>=BEDDING!\$A\$29,A\$1<=BEDDING!\$B\$29),AND(A\$1>=BEDDING!\$A\$30,A\$1<=BEDDING!\$B\$30),AND(A\$1>=BEDDING!\$A\$31,A\$1<=BEDDING!\$B\$31),AND(A\$1>=BEDDING!\$A\$32,A\$1<=BEDDING!\$B\$32),AND(A\$1>=BEDDING!\$A\$33,A\$1<=BEDDING!\$B\$33),AND(A\$1>=BEDDING!\$A\$34,A\$1<=BEDDING!\$B\$34),AND(A\$1>=BEDDING!\$A\$35,A\$1<=BEDDING!\$B\$35),AND(A\$1>=BEDDING!\$A\$36,A\$1<=BEDDING!\$B\$36),AND(A\$1>=BEDDING!\$A\$37,A\$1<=BEDDING!\$B\$37),AND(A\$1>=BEDDING!\$A\$38,A\$1<=BEDDING!\$B\$38),AND(A\$1>=BEDDING!\$A\$39,A\$1<=BEDDING!\$B\$39),AND(A\$1>=BEDDING!\$A\$40,A\$1<=BEDDING!\$B\$40),AND(A\$1>=BEDDING!\$A\$41,A\$1<=BEDDING!\$B\$41),AND(A\$1>=BEDDING!\$A\$42,A\$1<=BEDDING!\$B\$42),AND(A\$1>=BEDDING!\$A\$43,A\$1<=BEDDING!\$B\$43),AND(A\$1>=BEDDING!\$A\$44,A\$1<=BEDDING!\$B\$44),AND(A\$1>=BEDDING!\$A\$45,A\$1<=BEDDING!\$B\$45),AND(A\$1>=BEDDING!\$A\$46,A\$1<=BEDDING!\$B\$46),AND(A\$1>=BEDDING!\$A\$47,A\$1<=BEDDING!\$B\$47),AND(A\$1>=BEDDING!\$A\$48,A\$1<=BEDDING!\$B\$48),AND(A\$1>=BEDDING!\$A\$49,A\$1<=BEDDING!\$B\$49),AND(A\$1>=BEDDING!\$A\$50,A\$1<=BEDDING!\$B\$50),AND(A\$1>=BEDDING!\$A\$51,A\$1<=BEDDING!\$B\$51),AND(A\$1>=BEDDING!\$A\$52,A\$1<=BEDDING!\$B\$52),AND(A\$1>=BEDDING!\$A\$53,A\$1<=BEDDING!\$B\$53),AND(A\$1>=BEDDING!\$A\$54,A\$1<=BEDDING!\$B\$54),AND(A\$1>=BEDDING!\$A\$55,A\$1<=BEDDING!\$B\$55),AND(A\$1>=BEDDING!\$A\$56,A\$1<=BEDDING!\$B\$56),AND(A\$1>=BEDDING!\$A\$57,A\$1<=BEDDING!\$B\$57),AND(A\$1>=BEDDING!\$A\$58,A\$1<=BEDDING!\$B\$58),AND(A\$1>=BEDDING!\$A\$59,A\$1<=BEDDING!\$B\$59),AND(A\$1>=BEDDING!\$A\$60,A\$1<=BEDDING!\$B\$60),AND(A\$1>=BEDDING!\$A\$61,A\$1<=BEDDING!\$B\$61),AND(A\$1>=BEDDING!\$A\$62,A\$1<=BEDDING!\$B\$62),AND(A\$1>=BEDDING!\$A\$63,A\$1<=BEDDING!\$B\$63),AND(A\$1>=BEDDING!\$A\$64,A\$1<=BEDDING!\$B\$64),AND(A\$1>=BEDDING!\$A\$65,A\$1<=BEDDING!\$B\$65),AND(A\$1>=BEDDING!\$A\$66,A\$1<=BEDDING!\$B\$66),AND(A\$1>=BEDDING!\$A\$67,A\$1<=BEDDING!\$B\$67),AND(A\$1>=BEDDING!\$A\$68,A\$1<=BEDDING!\$B\$68),AND(A\$1>=BEDDING!\$A\$69,A\$1<=BEDDING!\$B\$69),AND(A\$1>=BEDDING!\$A\$70,A\$1<=BEDDING!\$B\$70),AND(A\$1>=BEDDING!\$A\$71,A\$1<=BEDDING!\$B\$71),AND(A\$1>=BEDDING!\$A\$72,A\$1<=BEDDING!\$B\$72),AND(A\$1>=BEDDING!\$A\$73,A\$1<=BEDDING!\$B\$73),AND(A\$1>=BEDDING!\$A\$74,A\$1<=BEDDING!\$B\$74),AND(A\$1>=BEDDING!\$A\$75,A\$1<=BEDDING!\$B\$75),AND(A\$1>=BEDDING!\$A\$76,A\$1<=BEDDING!\$B\$76),AND(A\$1>=BEDDING!\$A\$77,A\$1<=BEDDING!\$B\$77),AND(A\$1>=BEDDING!\$A\$78,A\$1<=BEDDING!\$B\$78),AND(A\$1>=BEDDING!\$A\$79,A\$1<=BEDDING!\$B\$79),AND(A\$1>=BEDDING!\$A\$80,A\$1<=BEDDING!\$B\$80),AND(A\$1>=BEDDING!\$A\$81,A\$1<=BEDDING!\$B\$81),AND(A\$1>=BEDDING!\$A\$82,A\$1<=BEDDING!\$B\$82),AND(A\$1>=BEDDING!\$A\$83,A\$1<=BEDDING!\$B\$83),AND(A\$1>=BEDDING!\$A\$84,A\$1<=BEDDING!\$B\$84),AND(A\$1>=BEDDING!\$A\$85,A\$1<=BEDDING!\$B\$85),AND(A\$1>=BEDDING!\$A\$86,A\$1<=BEDDING!\$B\$86),AND(A\$1>=BEDDING!\$A\$87,A\$1<=BEDDING!\$B\$87),AND(A\$1>=BEDDING!\$A\$88,A\$1<=BEDDING!\$B\$88),AND(A\$1>=BEDDING!\$A\$89,A\$1<=BEDDING!\$B\$89),AND(A\$1>=BEDDING!\$A\$90,A\$1<=BEDDING!\$B\$90),AND(A\$1>=BEDDING!\$A\$91,A\$1<=BEDDING!\$B\$91),AND(A\$1>=BEDDING!\$A\$92,A\$1<=BEDDING!\$B\$92),AND(A\$1>=BEDDING!\$A\$93,A\$1<=BEDDING!\$B\$93),AND(A\$1>=BEDDING!\$A\$94,A\$1<=BEDDING!\$B\$94),AND(A\$1>=BEDDING!\$A\$95,A\$1<=BEDDING!\$B\$95),AND(A\$1>=BEDDING!\$A\$96,A\$1<=BEDDING!\$B\$96),AND(A\$1>=BEDDING!\$A\$97,A\$1<=BEDDING!\$B\$97),AND(A\$1>=BEDDING!\$A\$98,A\$1<=BEDDING!\$B\$98),AND(A\$1>=BEDDING!\$A\$99,A\$1<=BEDDING!\$B\$99),AND(A\$1>=BEDDING!\$A\$100,A\$1<=BEDDING!\$B\$100)),1,0)

but I can't enter character more than 8192

so is there any solution for it or other way to solve it

best regards

9 Replies

# Re: how to inter formula in a cell has more 8192 characters

@alexanderjaml Can you disclose what you have in the BEDDING sheet and what output you expect in the progress chart? I'm pretty sure you don't need such a monstrous formula for whatever you are trying to do.

best response confirmed by alexanderjaml (Copper Contributor)
Solution

# Re: how to inter formula in a cell has more 8192 characters

Here are two different questions

1) how to highlight some cells

2) how to apply the formula in sample

First question - didn't catch the logic, not sure

Second question - use alternative formula which returns desired result. It could be

``=--(SUMPRODUCT( (A\$1>=Bedding!\$A\$1:A\$100)*(A\$1<=Bedding!\$B\$1:B\$100))>0)``

# Re: how to inter formula in a cell has more 8192 characters

yeah i find it

thank u so much @Riny_van_Eekelen

`=--(SUMPRODUCT( (A\$1>=Bedding!\$A\$1:A\$100)*(A\$1<=Bedding!\$B\$1:B\$100))>0)`

# Re: how to inter formula in a cell has more 8192 characters

thank you i appreciate your help

It works well@SergeiBaklan

# Re: how to inter formula in a cell has more 8192 characters

@alexanderjaml , glad to know it helped

# Re: how to inter formula in a cell has more 8192 characters

@SergeiBaklan can you kindly assist and shorten this formular for me

=(SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$5,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5)+SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$6,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5)+SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$7,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5)+SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$8,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5)+SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$9,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5)+SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$10,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5)+SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$11,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$12,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$13,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$14,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$15,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$16,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$17,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$18,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$19,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$20,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$21,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$22,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$23,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$24,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$25,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$26,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$27,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$28,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$29,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$30,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$31,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$32,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$33,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$34,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$35,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$36,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$37,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$38,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$39,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$40,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$41,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$42,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$43,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$44,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$45,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$46,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$47,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$48,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$49,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$50,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$51,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$52,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$53,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$54,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$55,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$56,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$57,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$58,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$59,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$60,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$61,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$62,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$63,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$64,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$65,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$66,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$67,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$68,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$69,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$70,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$71,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$72,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$73,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$74,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$75,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5) +SUMIFS(Table1[Girls],Table1[Newly Reached Individuals/Households/Activity?],Master!\$B\$5,Table1[WHAT Activities Indicator],Master!\$E\$76,Table1[Location LGA],Master!\$A85,Table1[Beneficiary Type],Master!\$F\$5,Table1[Reporting Month],\$G\$5))

Thank you.

# Re: how to inter formula in a cell has more 8192 characters

If I understood it correctly only Table1[WHAT Activities Indicator] is changing, when

``````=SUMPRODUCT(
SUMIFS(
Table1[Girls],
Table1[Newly Reached Individuals/Households/Activity?], Master!\$B\$5,
Table1[WHAT Activities Indicator], Master!\$E\$5:\$E\$76,
Table1[Location LGA], Master!\$A85,
Table1[Beneficiary Type], Master!\$F\$5,
Table1[Reporting Month], \$G\$5
)
)``````

# Re: how to inter formula in a cell has more 8192 characters

Dear Sergei,

Thank you very much for your assistance, I will like to add all the month which previously I used one month filtering, under the filter for month which I did this way

``````=SUMPRODUCT(
SUMIFS(
Table1[Girls],
Table1[Newly Reached Individuals/Households/Activity?], Master!\$B\$5,
Table1[WHAT Activities Indicator], Master!\$E\$5:\$E\$76,
Table1[Location LGA], Master!\$A85,
Table1[Beneficiary Type], Master!\$F\$5,
Table1[Reporting Month], \$G\$5:\$G\$16
)
)``````

But the numbers is drastically reducing, I don't know if I made mistake, I will appreciate your response.

# Re: how to inter formula in a cell has more 8192 characters

I assumed you have only one range as criteria, Master!\$E\$5:\$E\$76 , here is another one \$G\$5:\$G\$16

When perhaps

``````=SUMPRODUCT(
Table1[Girls] *
( Table1[Newly Reached Individuals/Households/Activity?] = Master!\$B\$5 ) *
MMULT(
--(Table1[WHAT Activities Indicator] = TRANSPOSE(Master!\$E\$5:\$E\$76)),
ROW(Master!\$E\$5:\$E\$76) / ROW(Master!\$E\$5:\$E\$76)
) *
( Table1[Location LGA] = Master!\$A85 ) *
( Table1[Beneficiary Type] = Master!\$F\$5 ) *
MMULT(
--(Table1[Reporting Month] = TRANSPOSE(\$G\$5:\$G\$16)),
ROW(\$G\$5:\$G\$16) / ROW(\$G\$5:\$G\$16)
)
)
``````

but that's not tested.

1 best response

Accepted Solutions
best response confirmed by alexanderjaml (Copper Contributor)
Solution

# Re: how to inter formula in a cell has more 8192 characters

Here are two different questions

1) how to highlight some cells

2) how to apply the formula in sample

First question - didn't catch the logic, not sure

Second question - use alternative formula which returns desired result. It could be

``=--(SUMPRODUCT( (A\$1>=Bedding!\$A\$1:A\$100)*(A\$1<=Bedding!\$B\$1:B\$100))>0)``