假设,四个工作表的第一行都作为表头,第二行开始输入正式内容,如果在第四个工作表的A2单元格输入年龄段(如17岁),在B2单元格中输入性别,需要在C2单元格求出符合两个要求的总人数,那么在C2单元格输入公式
=SUMPRODUCT((表1!$D$2:$D$3000=B2)*(表1!$G$2:$G$3000>=(TODAY()-LEFT(A2,2)*360))*(表1!$G$2:$G$3000<=(TODAY()=360-LEFT(A2,2)*360)))+SUMPRODUCT((表2!$D$2:$D$3000=B2)*(表2!$G$2:$G$3000>=(TODAY()-LEFT(A2,2)*360))*(表2!$G$2:$G$3000<=(TODAY()=360-LEFT(A2,2)*360)))+SUMPRODUCT((表3!$D$2:$D$3000=B2)*(表3!$G$2:$G$3000>=(TODAY()-LEFT(A2,2)*360))*(表3!$G$2:$G$3000<=(TODAY()=360-LEFT(A2,2)*360)))
即可,如有需要可以在A3,B3,A4,B4……单元格内输入相应的要求,然后将C2单元格下拉复制即可得到相应总人数
祝你成功!
温馨提示:答案为网友推荐,仅供参考