CODEWITHSUNDEEP
excelvbaexcel-formulasumsumifs
Bonnie Z
Bonnie Z

Reputation: 55

Summing a variable range

I'm needing to figure out how to sum a count who's length changes depending on criteria from other columns. I'm combining results from multiple spreadsheets. There will be duplicates in Columns A & B that I need to group then add the Count in Columns C together to get a total for both and the total needs to be reflected on the last line of the grouping. Source example:

Building#     Room#   Count    
1350          210      4    
1350          210      6    
1350          210      2    
1350          100      3    
1352          105      7    
1352          105      3    
1352          201      10

The results that I need to get needs to look like the below:

Building#     Room#   Count    Total    
1350          210      4    
1350          210      6    
1350          210      2        12    
1350          100      3         3    
1352          105      7    
1352          105      3        10    
1352          201      10       10

Upvotes: 0

Views: 65

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

IN D2 and pull down:

=IF(OR(A2<>A3,B2<>B3),SUM($C$2:C2)-SUM($D$1:D1),"")

enter image description here

Upvotes: 3

Pᴇʜ
Pᴇʜ

Reputation: 57683

Use this formula in D2 and pull it down:

=IF(COUNTIFS(A3:$A$1048576,A2,B3:$B$1048576,B2)=0,SUMIFS(C:C,A:A,A:A,B:B,B:B),"")

enter image description here

Upvotes: 2

Related Questions