不添加辅助列通过匹配表进行条件求和

Sapindus

各位大神，需求为：不添加辅助列，在匹配表找到对应的名称，并按照匹配后的名称进行求和。感谢！

无关岁月

1. =NPV(-2,SUMIFS(明细!$C:$C,明细!$A:$A,"<="&EOMONTH("2024年"&C$2,{-1,0}),明细!$B:$B,"*"&统计!$B4))

复制代码

quqiyuan

=SUMPRODUCT((VLOOKUP(明细!$B$2:$B$10,匹配表!$B:$C,2,)=$B4)*(--LEFTB(LOOKUP("座",$C$2:C$2),2)=MONTH(明细!$A$2:$A$10))*IF(MOD(COLUMN(A$1),2)=1,1,明细!$C$2:$C$10))
下拉右拉

boyuanoffice

把公式中的 明细!$D$2:$D$498=$B4 换为 RIGHT(明细!$B$2:$B$498)=$B4

edwin11891

金额：

1. =LET(sTab,明细!A$2:C$10,sMonth,CHOOSECOLS(sTab,1),sNAme,CHOOSECOLS(sTab,2),sNum,CHOOSECOLS(sTab,3),sClass,VLOOKUP(sNAme,匹配表!$B:$C,2,0),sum(FILTER(sNum,(sClass=B4)*(MONTH(sMonth)&"月"=C$2))))

复制代码

数量：

1. =LET(sTab,明细!A$2:C$10,sMonth,CHOOSECOLS(sTab,1),sNAme,CHOOSECOLS(sTab,2),sNum,CHOOSECOLS(sTab,3),sClass,VLOOKUP(sNAme,匹配表!$B:$C,2,0),ROWS(FILTER(sNum,(sClass=B4)*(MONTH(sMonth)&"月"=C$2))))

复制代码

mykitami

=SUMPRODUCT((TEXT(MONTH(明细!$A$2:$A$498),"0月")=C$2)*(RIGHT(明细!$B$2:$B$498)=$B4))

=SUMPRODUCT((TEXT(MONTH(明细!$A$2:$A$498),"0月")=C$2)*(RIGHT(明细!$B$2:$B$498)=$B4),明细!$C$2:$C$498)

或者直接合并为一个公式，下拉，右拉即可
=SUMPRODUCT((TEXT(MONTH(明细!$A$2:$A$498),"0月")=LOOKUP("座",$C$2:C$2))*(RIGHT(明细!$B$2:$B$498)=$B4)*(IF(C$3="数量",1,明细!$C$2:$C$498)))

我的城市你来过

=IF(C$3="数量",COUNT(IF((MONTH(明细!$A$2:$A$10)=MAX(IFERROR(SUBSTITUTE($C$2:C$2,"月","")/1,0)))*(RIGHT(明细!$B$2:$B$10,1)=$B4),明细!$C$2:$C$10,"")),SUM(IF((MONTH(明细!$A$2:$A$10)=MAX(IFERROR(SUBSTITUTE($C$2:C$2,"月","")/1,0)))*(RIGHT(明细!$B$2:$B$10,1)=$B4),明细!$C$2:$C$10,"")))

一烟难禁

数据很规律，感觉没有必要匹配

微光1983

=SUMPRODUCT((TEXT(MONTH(明细!$A$2:$A$498),"0月")=C$2)*(明细!$B$2:$B$498=XLOOKUP($B4,匹配表!C:C,匹配表!B:B)),明细!$C$2:$C$498)

微光1983

微光1983 发表于 2024-8-20 14:35
=SUMPRODUCT((TEXT(MONTH(明细!$A$2:$A$498),"0月")=C$2)*(明细!$B$2:$B$498=XLOOKUP($B4,匹配表!C:C,匹配 ...

感觉没有这么复杂 替换 嵌套一个查找匹配就好了 不用加辅助列