Генеричка формула
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Резиме
Да бисте бројали видљиве редове само са критеријумима, можете користити прилично сложену формулу засновану на СУМПРОДУЦТ, СУБТОТАЛ и ОФФСЕТ. У приказаном примеру, формула у Ц12 је:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Предговор
Функција СУБТОТАЛ може лако генерирати суме и бројеве за скривене и нескривене редове. Међутим, он није у стању да обрађује критеријуме попут ЦОУНТИФ или СУМИФ без неке помоћи. Једно од решења је употреба СУМПРОДУЦТ за примену функције СУБТОТАЛ (путем ОФФСЕТ) и критеријума. Детаљи овог приступа описани су у наставку.
Објашњење
У основи, ова формула делује постављањем два низа унутар СУМПРОДУЦТ-а. Први низ примењује критеријуме, а други низ обрађује видљивост:
=SUMPRODUCT(criteria*visibility)
Критеријуми се примењују са делом формуле:
=(C5:C8=C10)
Који генерише овакав низ:
(FALSE;TRUE;FALSE;TRUE)
Тамо где ИСТИНА значи „испуњава критеријуме“. Имајте на уму да на овом низу користимо множење (*), вредности ТРУЕ ФАЛСЕ аутоматски ће се претворити у 1 и 0 помоћу математичке операције, па ћемо на крају добити:
(0;1;0;1)
Филтер видљивости примењује се помоћу СУБТОТАЛ, са функцијом број 103.
СУБТОТАЛ је у стању да изузме скривене редове приликом извођења прорачуна, па га у овом случају можемо користити за генерисање „филтера“ за изузеће скривених редова унутар СУМПРОДУЦТ-а. Међутим, проблем је што СУБТОТАЛ враћа један број, док нам је потребан низ резултата да бисмо га успешно користили унутар СУМПРОДУЦТ-а. Трик је у томе да се користи ОФФСЕТ за додавање СУБТОТАЛ-а једне референце по реду, тако да ће ОФФСЕТ вратити један резултат по реду.
Наравно, за то је потребан још један трик, а то је давање ОФФСЕТ-а низа који садржи један број по реду, почевши од нуле. То радимо са изразом изграђеним на функцији РОВ:
=ROW(C5:C8)-MIN(ROW(C5:C8)
који ће генерисати овакав низ:
(0;1;2;3)
Укратко, други низ (који управља видљивошћу користећи СУБТОТАЛ), генерише се овако:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
И, коначно, имамо:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Који враћа 1.
Више критеријума
Можете проширити формулу тако да обрађује више критеријума попут овог:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Збрајање резултата
Да бисте вратили збир вредности уместо бројања, можете прилагодити формулу тако да укључује опсег збира:
=SUMPRODUCT(criteria*visibility*sumrange)
Критеријуми и низови видљивости раде на исти начин као што је претходно објашњено, искључујући ћелије које нису видљиве. Ако вам је потребно делимично подударање, можете конструисати израз помоћу ИСУМБЕР + СЕАРЦХ, као што је овде објашњено.
