sumifs only first occurrence based on two criteria

[aroramon]

hi all,

I have data that's formatted as follows:

[TABLE="width: 204"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Col_A
Ontario[/TD]
[TD]Col_B
Ontario1[/TD]
[TD="align: right"]Col_C
1[/TD]
[/TR]
[TR]
[TD]Ontario[/TD]
[TD]Ontario1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Ontario[/TD]
[TD]Ontario2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Ontario[/TD]
[TD]Ontario2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Ontario[/TD]
[TD]Ontario2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Alberta[/TD]
[TD]Alberta1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Alberta[/TD]
[TD]Alberta1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Alberta[/TD]
[TD]Alberta1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Alberta[/TD]
[TD]Alberta2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Alberta[/TD]
[TD]Alberta3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Alberta[/TD]
[TD]Alberta3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Manitoba[/TD]
[TD]Manitoba1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Manitoba[/TD]
[TD]Manitoba2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Manitoba[/TD]
[TD]Manitoba2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Manitoba[/TD]
[TD]Manitoba2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Manitoba[/TD]
[TD]Manitoba3[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Manitoba[/TD]
[TD]Manitoba3[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

on another sheet, I am looking to add a sumifs type formula based on column A which sums the data in column C, but only for the first instance of whatever value is in column b. i.e. sum for Ontario should be 1+2 = 3. Sum for Alberta should be 12. Sum for Manitoba should be 21. Thank you in advance for any help!

 

[MrPink1986]

This formula will sum the data for you on the same sheet - =SUMIFS($C$2:$C$18,$A$2:$A$18,A2,$B$2:$B$18,B2).

 

[Peter_SSs]

.. sums the data in column C, but only for the first instance of whatever value is in column b. i.e. sum for Ontario should be 1+2 = 3. Sum for Alberta should be 12. Sum for Manitoba should be 21.

Try this, copied down. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	Col_A	Col_B	Col_C
2	Ontario	Ontario1	1			Ontario	3
3	Ontario	Ontario1	1			Alberta	12
4	Ontario	Ontario2	2			Manitoba	21
5	Ontario	Ontario2	2
6	Ontario	Ontario2	2
7	Alberta	Alberta1	3
8	Alberta	Alberta1	3
9	Alberta	Alberta1	3
10	Alberta	Alberta2	4
11	Alberta	Alberta3	5
12	Alberta	Alberta3	5
13	Manitoba	Manitoba1	6
14	Manitoba	Manitoba2	7
15	Manitoba	Manitoba2	7
16	Manitoba	Manitoba2	7
17	Manitoba	Manitoba3	8
18	Manitoba	Manitoba3	8
19
SUM