Excel - (Array Formula) Sum IF Multiple Criteria w

问题:

I need to understand how (via an array formula) to sum up results based on multiple criteria. I understand there are plenty of questions on this topic already answered but mine seems to be different so the solutions given already don't work to the best of my knowledge.

As an example, see the below table. What I am wanting to do is sum all of Val for Type A where there is no Type C on the same day. (ie day 5 & 7)

Day     Type    Val
1       A       5
1       B       6
1       C       9
2       B       2
2       A       8
2       C       3
3       C       4
3       B       2
3       A       2
4       A       5
4       B       9
4       C       8
5       A       7
5       B       5
6       A       6
6       B       3
6       C       4
7       A       7
7       B       9

回答1:

1 cell array formula in J2 is:

=SUM((B2:B20="A")*C2:C20*ISERROR(MATCH(A2:A20,IF(A2:A20&B2:B20=A2:A20&"C",A2:A20),0)))

And to understand what's happening I have created I2 formula using helper columns. "_S1", "_S2" and "_S3" are named ranges below the headers.

I2 is normal formula

=SUMPRODUCT(_S1,_S3)

_S1 is array formula

=(B2:B20="A")*C2:C20

Here I am just returning values for Type "A" and zeros for everything else.

_S2 is array formula

=IF(A2:A20&B2:B20=A2:A20&"C",A2:A20)

Here I am creating a range with day numbers where I have a "C" and some additional FALSEs.

_S3 is array formula

=ISERROR(MATCH(A2:A20,_S2,0))*1

Here I try to match a day from column A to 'C-days' from range _S2. If there is a match I return 0, otherwise 1.

回答2:

May I offer a slightly simplified version of that cool formula.

=SUM((B2:B20="A")*C2:C20*ISERROR(MATCH(A2:A20,(B2:B20="C")*A2:A20,0)))