求解？

lccming2013 · 发表于 2021-12-17 00:06

求大神，帮看看公式错哪了？
=SUMPRODUCT(SUMIFS(Sheet1!E:E,Sheet1!B:B,B3,Sheet1!C:C,">=2019-1-1",Sheet1!C:C,"<2020-1-1"),{"92号汽油(国Ⅵ)";"92号汽油(国Ⅴ)"}

249773986 · 发表于 2021-12-17 07:38

上传附件看看

一剑惊心 · 发表于 2021-12-17 09:18

最后二个汽油是什么鬼？

lccming2013 · 发表于 2021-12-19 02:02

大神们，帮看看公式错哪了？

lccming2013 · 发表于 2021-12-27 23:32

249773986 发表于 2021-12-17 07:38
上传附件看看

麻烦帮看看，谢谢！

tchh666 · 发表于 2021-12-28 20:26

本帖最后由 tchh666 于 2021-12-28 21:35 编辑

D3=SUMPRODUCT((Sheet1!$B$3:$B$21=$B3)*(LEFT(Sheet1!$F$3:$F$21,2)=LEFT($C3,2))*(TEXT(Sheet1!$C$3:$C$21,"YYYY年")=LOOKUP(1,0/($D$1:D$1<>""),$D$1:D$1))*IF(D$2="数量(升)",Sheet1!$G$3:$G$21,Sheet1!$E$3:$E$21)) 右拉下拉，，

tchh666 · 发表于 2021-12-28 21:11

本帖最后由 tchh666 于 2021-12-28 21:37 编辑

=SUMPRODUCT((Sheet1!$B$3:$B$21=$B3)*(LEFT(Sheet1!$F$3:$F$21,2)=LEFT($C3,2))*(TEXT(Sheet1!$C$3:$C$21,"YYYY年")=LOOKUP(1,0/($D$1:D$1<>""),$D$1:D$1))*IF(D$2="数量(升)",Sheet1!$G$3:$G$21,Sheet1!$E$3:$E$21))

丢丢表格 · 发表于 2021-12-28 21:24

lccming2013 发表于 2021-12-19 02:02
大神们，帮看看公式错哪了？

D3
=SUMIFS(Sheet1!G:G,Sheet1!B:B,B3,Sheet1!C:C,">2016-0",Sheet1!C:C,"<2017-0")

E3
=SUMIFS(Sheet1!E:E,Sheet1!B:B,B3,Sheet1!C:C,">2016-0",Sheet1!C:C,"<2017-0")

hbzql · 发表于 2021-12-29 01:04

=SUM(($B3=Sheet1!$B$3:$B$21)*NOT(ISERROR(FIND($C3,Sheet1!$F$3:$F$21)))*(LEFT(LOOKUP(1,0/($D$1:D$1<>""),$D$1:D$1),4)*1=YEAR(Sheet1!$C$3:$C$21))*INDEX(Sheet1!$E$3:$G$21,,MATCH(LEFT(D$2,2),Sheet1!$E$2:$G$2,0)))

		自动登录	找回密码
密码			免费注册

[求助] 求解？