Обща формула
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Обобщение
За да сумирате условно идентични диапазони, които съществуват в отделни работни листове, всички в една формула, можете да използвате функцията SUMIF с INDIRECT, увита в SUMPRODUCT. В показания пример формулата в C9 е:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Обяснение
Данните за всеки от трите обработвани листа изглеждат така:
Първо, обърнете внимание, че не можете да използвате SUMIF с "нормална" 3D препратка като тази:
Sheet1:Sheet3!D4:D5
Това е стандартният "3D синтаксис", но ако се опитате да го използвате със SUMIF, ще получите грешка #VALUE. Така че, за да заобиколите този проблем, можете да използвате именуван диапазон "листове", който изброява всеки лист (раздел работен лист), който искате да включите. За да изградим референции, които Excel ще интерпретира правилно, трябва да обединим имената на листове в диапазоните, с които трябва да работим, и след това да използваме INDIRECT, за да накараме Excel да ги разпознае правилно.
Също така, тъй като посоченият диапазон "листове" съдържа множество стойности (т.е. неговия масив), резултатът от SUMIF в този случай също е масив (понякога наричан "резултатен масив). Така че ние използваме SUMPRODUCT, за да го обработваме, тъй като има способността да обработва масивите в оригинал, без да изисква Ctrl-Shift-Enter, както много други формули на масиви.
Друг начин
Примерът по-горе е малко сложен. Друг начин за справяне с този проблем е да направите "локална" условна сума на всеки лист, след което да използвате обикновена 3D сума, за да добавите всяка стойност в раздела обобщение.
За да направите това, добавете формула SUMIF към всеки лист, който използва клетка с критерии на обобщения лист. След това, когато промените критериите, всички свързани формули SUMIF ще се актуализират.
