SUM two lists in a SUMIF formula

Brass Contributor

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?

2 Replies

@JennySommet 

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"))

Thank you for your help, I will try it, although it's a lot of brackets for me! I guess the Sumif can only cope with so much, shame it can't handle two lists, I thought there might be a way.