用友G6如何开张

SUMIFS(2/2)

———————————————

接上回SUMIFS(1/2)

范例3：对特定条件的降雨量求和

B7:=SUMIFS(B2:E2,B3:E3, ">=20",B4:E4, "<10")

C7:{=SUM((B3:E3>=20)*(B4:E4<10)*(B2:E2))}

D7:=SUMPRODUCT((B3:E3>20)*(B4:E4<10),(B2:E2))

B9:=SUMIFS(B2:E3, B4:E5, ">=20", B6:E7, "<10")

C9:{=SUM((B4:E5>=20)*(B6:E7<10)*(B2:E3))}

D9:=SUMPRODUCT((B4:E5>=20)*(B6:E7<10),B2:E3)

示例 4：以引用形式或使用通配符输入条件

B8:=SUMIFS(G2:G6,C2:C6,">2",E2:E6,"是",F2:F6,">1999",F2:F6,"<2004")

B9:=AVERAGEIFS(G2:G6,C2:C6,">2",E2:E6,"是",F2:F6,">1999",F2:F6,"<2004")

B10:=SUMIFS(G2:G6,C2:C6,">2",E2:E6,"是",F2:F6,">1999",F2:F6,"<2004",B2:B6,">2000")

示例 5：综合应用

底稿数据22行，为节省篇幅，从13行其切到了右侧的图内，辅助列为自行添加的，有些函数直接写成A2:A22&B2:B22不被接受，辅助列也是常见解决方法，甚至隐藏的辅助页。

下面数据量比较大，别怕麻烦，一行行的看，一行行的品，数组是需要时间慢慢理解的。

F2:=SUMIFS(C2:C22,A2:A22,"A")

G2:=SUMPRODUCT(C2:C22*(A2:A22="A"))

H2:=SUMIF(A2:A22,"A",C2:C22)

F3:=SUMIFS(C2:C22,A2:A22,"A",B2:B22,1)

G3:=SUMPRODUCT(C2:C22*(A2:A22="A")*(B2:B22=1))

I3:{=SUM(IF((A2:A22="A")*(B2:B22=1),C2:C22,0))}

F4:=SUM(SUMIFS(C2:C22,A2:A22,"A",B2:B22,{1,3}))

G4:=SUMPRODUCT(C2:C22*(A2:A22="A")*(B2:B22={1,3}))

I4:{=SUM(IF((A2:A22="A")*(B2:B22={1,3}),C2:C22,0))}

F5:=SUM(SUMIFS(C2:C22,A2:A22,{"A","C"}))

G5:=SUMPRODUCT(C2:C22*(A2:A22={"A","C"}))

H5:=SUM(SUMIF(A2:A22,{"A","C"},C2:C22))

F6:=SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,1))

G6:=SUMPRODUCT(C2:C22*(A2:A22={"A","C"})*(B2:B22=1))

I6:{=SUM(IF((A2:A22={"A","C"})*(B2:B22=1),C2:C22,0))}

F7:=SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1,3}))

G7:=SUMPRODUCT(C2:C22*(A2:A22={"A","C"})*(B2:B22={1,3}))

I7:{=SUM(IF((A2:A22="A")*(B2:B22=1)+(A2:A22="C")*(B2:B22=3),C2:C22,0))}

F8:=SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1;3}))

G8:=SUMPRODUCT(C2:C22*(A2:A22="A")*(B2:B22={1,3}))+SUMPRODUCT(C2:C22*(A2:A22="C")*(B2:B22={1,3}))

I8:{=SUM(IF((A2:A22&B2:B22={"A1","C1","A3","C3"}),C2:C22,0))}

F9:=SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1;3;4}))

G9:=SUMPRODUCT(C2:C22*(A2:A22="A")*(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22*(A2:A22="C")*(B2:B22={1,3,4}))

I9:{=SUM(IF((A2:A22&B2:B22={"A1","C1","A3","C3","A4","C4"}),C2:C22,0))}

F10:=SUM(SUMIFS(C2:C22,A2:A22,{"A","B","C"},B2:B22,{1;3;4}))

G10:=SUMPRODUCT(C2:C22*(A2:A22="A")*(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22*(A2:A22="B")*(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22*(A2:A22="C")*(B2:B22={1,3,4}))

I10:{=SUM(IF((A2:A22&B2:B22={"A1","B1","C1","A3","B3","C3","A4","B4","C4"}),C2:C22,0))}

F11:=COUNTIFS(A2:A22,"A")

G11:=SUMPRODUCT(--(A2:A22="A"))

I11:{=SUM(IF(A2:A22="A",1))}

J11:=COUNTIF(A2:A22,"A")

F12:=SUM(COUNTIFS(A2:A22,{"A","B"}))

G12:=SUMPRODUCT(--(A2:A22={"A","B"}))

I12:{=SUM(IF(A2:A22={"A","B"},1))}

J12:=SUM(COUNTIF(A2:A22,{"A","B"}))

F13:=SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,1))

G13:=SUMPRODUCT((A2:A22={"A","B"})*(B2:B22=1))

I13:{=SUM(IF((A2:A22={"A","B"})*(B2:B22=1),1))}

F14:=SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,{1;3}))

G14:=SUMPRODUCT((A2:A22={"A","B"})*(B2:B22=1))+SUMPRODUCT((A2:A22={"A","B"})*(B2:B22=3))

I14:{=SUM(IF((A2:A22&B2:B22={"A1","B1","A3","B3"}),1))}

F15:=SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,{1,3}))

G15:=SUMPRODUCT((A2:A22={"A","B"})*(B2:B22={1,3}))

F16:=SUMPRODUCT(1/COUNTIFS(A2:A22,A2:A22,B2:B22,B2:B22))

G16:=SUMPRODUCT(--(MATCH(A2:A22&B2:B22,A2:A22&B2:B22,)=ROW(A2:A22)-1))

I16:{=SUM((MATCH(A2:A22&B2:B22,A2:A22&B2:B22,)=ROW(A2:A22)-1)*1)}

J16:=SUMPRODUCT(1/COUNTIF(D2:D22,D2:D22))

随堂小测试：

上面的问题已经很费眼睛了，留个简单的题外小测试，如何提取单元格内公式形成上面的格式，用ADDRESS()&":"&FORMULATEXT()搞定。

这里好好看看ADDRESS函数，该函数不单独讲解，在编制多下属机构用友UFO损益一览表的时候，可以通过这个函数批量拼合变量（账套号、科目编码、辅助核算等）的字符串，再直接导入用友的批量公式内即可完成。

另外，有的时候我们需要针对一张表了解哪些是公式、哪些是数值，可以在条件格式内使用=AND(单元格<>"",ISFORMULA(单元格))，将用公式的单元格标出背景色。