sumifs only first occurrence based on two criteria

[aroramon]

hi all,

I have data that's formatted as follows:

Col_A Ontario	Col_B Ontario1	Col_C 1
Ontario	Ontario1	1
Ontario	Ontario2	2
Ontario	Ontario2	2
Ontario	Ontario2	2
Alberta	Alberta1	3
Alberta	Alberta1	3
Alberta	Alberta1	3
Alberta	Alberta2	4
Alberta	Alberta3	5
Alberta	Alberta3	5
Manitoba	Manitoba1	6
Manitoba	Manitoba2	7
Manitoba	Manitoba2	7
Manitoba	Manitoba2	7
Manitoba	Manitoba3	8
Manitoba	Manitoba3	8

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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