hi all,
i am new here, but i love the place, i have a question, i have a formula, but it is a bit long, i need to make it shorter, an example is below:
=SUMPRODUCT(COUNTIFS(INDIRECT("Sheet2"&"!a:a"),"c",INDIRECT("Sheet2"&"!b:b"),"no"))+SUMPRODUCT(COUNTIFS(INDIRECT("Sheet3"&"!a:a"),"c",INDIRECT("Sheet3"&"!b:b"),"no"))+SUMPRODUCT(COUNTIFS(INDIRECT("Sheet4"&"!a:a"),"c",INDIRECT("Sheet4"&"!b:b"),"no"))+SUMPRODUCT(COUNTIFS(INDIRECT("Sheet5"&"!a:a"),"c",INDIRECT("Sheet5"&"!b:b"),"no"))
i will be having about 30 sheets so it is very long, anyone can help me to simplify this,
thanks in advance,