sumifs only first occurrence based on two criteria

aroramon

New Member
Joined
Feb 16, 2010
Messages
2
hi all,

I have data that's formatted as follows:

Col_A
Ontario
Col_B
Ontario1
Col_C
1
OntarioOntario11
OntarioOntario22
OntarioOntario22
OntarioOntario22
AlbertaAlberta13
AlbertaAlberta13
AlbertaAlberta13
AlbertaAlberta24
AlbertaAlberta35
AlbertaAlberta35
ManitobaManitoba16
ManitobaManitoba27
ManitobaManitoba27
ManitobaManitoba27
ManitobaManitoba38
ManitobaManitoba38

<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!
 

Some videos you may like

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
Joined
May 1, 2012
Messages
250
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
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
.. 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:

Watch MrExcel Video

Forum statistics

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