Mar 20 2021 03:48 AM
Hello,
My current formula with two lists within a SUMIF does not work:
=SUM(SUMIFS('Pipeline Data'!S:S,'Pipeline Data'!Z:Z,A14,'Pipeline Data'!P:P,$A$2,'Pipeline Data'!AC:AC,{"Admissions File Pending","Applied","Admitted","Pre-enrolled"},'Pipeline Data'!AA:AA,"0",'Pipeline Data'!BJ:BJ,{"Remote","Blended"},'Pipeline Data'!CA:CA,"0"))
Instead I have to use 2 lots of formula like this:
=SUM(SUMIFS('Pipeline Data'!S:S,'Pipeline Data'!Z:Z,A13,'Pipeline Data'!P:P,$A$2,'Pipeline Data'!AC:AC,{"Admissions File Pending","Applied","Admitted","Pre-enrolled"},'Pipeline Data'!AA:AA,"0",'Pipeline Data'!BJ:BJ,"Remote",'Pipeline Data'!CA:CA,"0"))
+SUM(SUMIFS('Pipeline Data'!S:S,'Pipeline Data'!Z:Z,A13,'Pipeline Data'!P:P,$A$2,'Pipeline Data'!AC:AC,{"Admissions File Pending","Applied","Admitted","Pre-enrolled"},'Pipeline Data'!AA:AA,"0",'Pipeline Data'!BJ:BJ,"Blended",'Pipeline Data'!CA:CA,"0"))
How can I avoid such a long formula and keep two lists within one like the first one?
Mar 20 2021 05:31 AM
You could do this,, but it will be less efficient, so I recommend using finite ranges instead of entire columns:
=SUMPRODUCT('Pipeline Data'!$S$1:$S$100,('Pipeline Data'!$Z$1:$Z$100=A14)*('Pipeline Data'!$P$1:$P$100=$A$2)*(('Pipeline Data'!$AC$1:$AC$100="Admissions File Pending")+('Pipeline Data'!$AC$1:$AC$100="Applied")+('Pipeline Data'!$AC$1:$AC$100="Admitted")+('Pipeline Data'!$AC$1:$AC$100="Pre-enrolled"))*('Pipeline Data'!$AA$1:$AA$100="0")*(('Pipeline Data'!$BJ$1:$BJ$100="Remote")+('Pipeline Data'!$BJ$1:$BJ$100="Blended"))*('Pipeline Data'!$CA$1:$CA$100="0"))
Mar 26 2021 01:11 AM