![大型 SUMIFS 无法正常工作](https://linux22.com/image/1509823/%E5%A4%A7%E5%9E%8B%20SUMIFS%20%E6%97%A0%E6%B3%95%E6%AD%A3%E5%B8%B8%E5%B7%A5%E4%BD%9C.png)
因此我有一个如下所示的 sumifs 函数:
=SUMIFS(Data!$O:$O,Data!$C:$C,$I$120,Data!$R:$R,L$120,Data!$I:$I,{6002,6004,6006,6008,6010,6012,6014,6016,6065,6702,6099,6302,6304,6306,6307,6308,6309,6310,6312,6961,6998,6999,9100,9101,9102,9103,9104,9105,9106,9107,9108,9110,9117,9122,6402,6404,6406,6408,6412,6414,6415,6416,6418,6420,6422,6424,6426,6428,6430,6432,6434,6436,6438,6440,6499,6502,6504,6506,6508,6510,6512,6514,6516,6518,6520,6522,6524,6526,6599,6602,6603,6604,6606,6608,6610,6612,6614,6616,6618,6620,6621,6622,6624,6626,6628,6630,6631,6632,6634,6636,6638,6650,6699,6701,6704,6706,6708,6710,6712,6714,6749,6751,6752,6754,6756,6758,6760,6762,6764,6766,6768,6770,6772,6779,6782,6784,6786,6788,6790,6792,6794,6796,6798,6800,6802,6804,6849,6850,6852,6854,6856,6858,6860,6862,6864,6865,6866,6867,6868,6870,6872,6874,6876,6878,6879,6880,6882,6884,6886,6888,6892,6894,6896,6899,6902,6904,6906,6908,6910,6912,6914,6916,6919,6920,6921,6922,6923,6924,6925,6926,6927,6928,6929,6930,6931,6932,6933,6934,6936,6937,6938,6939,6940,6941,6942,6943,6944,6945,6946,6947,6948,6949,6997,6950,6951,6952,6953,6954,6955,6956,6957,6958,6959,6960})
哎呀,好大啊。它不能正常工作,只能计算为 0。但是,它的同类却工作正常:
=SUMIFS(Data!$O:$O,Data!$C:$C,$I$120,Data!$R:$R,M$120,Data!$I:$I,{9103,9105})
请忽略其他数字的细微变化 - 公式是在大型网格中计算的,除此行之外的所有内容都运行良好。问题似乎在于在 中使用大型数组作为我的 OR 函数SUMIFS
。
我希望找到一种可以避免 的解决方案SUMIFS(....{1,2,3})+SUMIFS(...{4,5,6})
,但我不确定为什么它不起作用。我遇到了某种数组限制吗?