I have a nested SUMIF statement that sums data from Sheet2 based on criteria from drop-down list cells in the current sheet.
Cell A contains city names...
---(If A2 = "Albuquerque", then it will sum the data for Albuquerque from Column A in Sheet2)
Column B contains months... ---(If B2 = "January 2019", then it will sum the data for January 2019 from Column B in Sheet2)
The fundamental formula is...
=IF(AND($A$2="",$B$2=""),SUM('Sheet 2'!J:J),IF(AND($A$2<>"",$B$2=""),SUMIF(Sheet 2'!A:A,A2,'Sheet 2'!J:J),IF(AND($A$2="",$B$2<>""),SUMIF(Sheet 2'!B:B,B2,'Sheet 2'!J:J),IF(AND($T$2<>"",$T$3<>""),SUMIFS('Sheet 2'!J:J,'Sheet 2'!A:A,A2,'Sheet 2'!B:B,B2))))
This formula works so long as one value is selected for the drop-down list cell.
But the drop-down lists have multi-select enabled with a comma separating both values. And if I select more than one item from the drop-down in the drop-down list cells, the SUMIFS formula doesn't understand and returns nothing.
---i.e.: If I select Albuquerque and Phoenix in cell A2, A2 will show "Albuquerque, Phoenix". But the SUMIF will not recognize this and will return 0 instead of data for both Albuquerque and Phoenix.
I'm wondering if there is a way to engineer my formula in a way that will recognize more than two items in the drop-down list cell and filter the data according to both of those values.