Forum Discussion
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)
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)thank you i appreciate your help
It works wellSergeiBaklan
alexanderjaml , glad to know it helped
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.
