Geek Answers Handbook

Excel - (array formula) IF sum with multiple criteria with difference

I need to understand how (using an array formula) to summarize results based on several criteria. I understand that there are already many questions on this topic, but mine seem to be different, so the solutions provided no longer work, as far as I know.

See the table below for an example. What I want to do is sum all Val values ​​for type A, where there is no type C on the same day. (i.e. 5 and 7 days)

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
0
source share
1 answer

Result

1 cell array formula in J2:

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

, , I2, . "_S1", "_S2" "_S3" .

I2 -

=SUMPRODUCT(_S1,_S3)

_S1 -

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

"" .

_S2 -

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

, "C" FALSE.

_S3 -

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

A "C-" _S2. , 0, 1.

0

More articles:


All Articles