COUNTIFS closed workbook

[jjcrazy]

I'm trying to rewrite a COUNTIFS function so i can use it with an closed workbook, by using COUNT(IF(AND...

I want to count the number of data that has B3 in column AW, E2 in column AN and D3 in column AU. The following function works for that:

=COUNTIFS('data'!$AW:$AW;$B3;'data'!$AN:$AN;E$2;'data'!$AU:$AU;$D3)

Now I tried to rewrite it as follows, but this did not work:

=COUNT(IF(AND('data'!$AW$1:$AW$10000=$B3;'data'!$AN$1:$AN$10000=L$2;'data'!$AU$1:$AU$10000=$D3);1))

What did I do wrong here?

 

[RoryA]

AND doesn't work that way with arrays - it returns one result, not an array of results. You can use:

=SUMPRODUCT(('data'!$AW$1:$AW$10000=$B3)*('data'!$AN$1:$AN$10000=L$2)*('data'!$AU$1:$AU$10000=$D3))

 

[jjcrazy]

Thanks so much, it worked!

Could I also replace a sumifs function?

 

[RoryA]

Yes - similar principle, just add the sum range to the end.

 

[jjcrazy]

thanks!! The only problem i have now is that it made my workbook take forever to load..

 

[RoryA]

Maybe you should consider a pivot table instead.

 

[jjcrazy]

i've actually made this to replace a pivot table, as i need the costs of deliveries to different zipcodes, but these can differ and i need to link something else to it. So i need everything to be in the same place every time..

 

[RoryA]

You lost me. Why not a pivot table and then use GETPIVOTDATA to return the data you need wherever you need it?