Countifs and closed workbooks

[trone77]

The following formula counts the various pairs of values that appear in file ‘[2021.xlsb] and if they are equal to cell ‘[2021.xlsb]Fri 2,3,4!$B$5) it will return the value “x”:



=IF(COUNTIFS('[2021.xlsb]Fri 2,3,4'!$H$5:$H$49,$C9,'[2021.xlsb]Fri 2,3,4'!$I$5:$I$49,$D9,'[2021.xlsb]Fri 2,3,4'!$AB$5:$AB$49,'[2021.xlsb]Fri 2,3,4'!$B$5)+COUNTIFS('[2021.xlsb]Fri 2,3,4'!$J$5:$J$49,$C9,'[2021.xlsb]Fri 2,3,4'!$K$5:$K$49,$D9,'[2021.xlsb]Fri 2,3,4'!$AB$5:$AB$49,'[2021.xlsb]Fri 2,3,4'!$B$5)+COUNTIFS('[2021.xlsb]Fri 2,3,4'!$L$5:$L$49,$D9,'[2021.xlsb]Fri 2,3,4'!$M$5:$M$49,$E9,'[2021.xlsb]Fri 2,3,4'!$AB$5:$AB$49,'[2021.xlsb]Fri 2,3,4'!$B$5)+COUNTIFS('[2021.xlsb]Fri 2,3,4'!$N$5:$N$49,$D9,'[2021.xlsb]Fri 2,3,4'!$O$5:$O$49,$E9,'[2021.xlsb]Fri 2,3,4'!$AB$5:$AB$49,'[2021.xlsb]Fri 2,3,4'!$B$5)+COUNTIFS('[2021.xlsb]Fri 2,3,4'!$P$5:$P$49,$C9,'[2021.xlsb]Fri 2,3,4'!$Q$5:$Q$49,$E9,'[2021.xlsb]Fri 2,3,4'!$AB$5:$AB$49,'[2021.xlsb]Fri 2,3,4'!$B$5)+COUNTIFS('[2021.xlsb]Fri 2,3,4'!$R$5:$R$49,$C9,'[2021.xlsb]Fri 2,3,4'!$S$5:$S$49,$E9,'[2021.xlsb]Fri 2,3,4'!$AB$5:$AB$49,'[2021.xlsb]Fri 2,3,4'!$B$5),"x","")



However since I can using countifs with this formula, it does not execute unless the file ‘[2020.xlsb] is open. How would this formula look using Sumproduct that would allow me to still execute while the file is closed?

 

[jasonb75]

When changing countifs to sumproduct you need to add a few extra parenthesis / brackets and change the commas to either = or *

I've shown a simplified before and after below with a description of the changes that you need to make. Hope it makes sense.

COUNTIFS(range1,criteria1,range2,criteria2)

Make the first and last brackets double.
Change the comma between each range and the criteria for that range to =
Change the comma between a criteria and the next range to )*(

SUMPRODUCT((range1=criteria1)*(range2=criteria2))

It is possible that the formula could possibly be simplified significantly if data allows it. For example if column H, J, L, etc contain text while columns I, K, M, etc contain numbers.