# sumifs only first occurrence based on two criteria

#### aroramon

##### New Member
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!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### MrPink1986

##### Active Member
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

##### MrExcel MVP, Moderator
.. 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
ABCDEFG
1Col_ACol_BCol_C
2OntarioOntario11Ontario3
3OntarioOntario11Alberta12
4OntarioOntario22Manitoba21
5OntarioOntario22
6OntarioOntario22
7AlbertaAlberta13
8AlbertaAlberta13
9AlbertaAlberta13
10AlbertaAlberta24
11AlbertaAlberta35
12AlbertaAlberta35
13ManitobaManitoba16
14ManitobaManitoba27
15ManitobaManitoba27
16ManitobaManitoba27
17ManitobaManitoba38
18ManitobaManitoba38
19
SUM

Last edited:

Replies
0
Views
184
Replies
5
Views
3K
Replies
3
Views
212
Replies
13
Views
373
Replies
1
Views
273

1,109,533
Messages
5,529,401
Members
409,871
Latest member
i1patrick