CODEWITHSUNDEEP
excel
MR K
MR K

Reputation: 113

EXcel formula : Summation based on same numeric input

The requirement is that I have the following sample data in Excel 1 

ticket      hours
----------+------    
1.131233    44   
2.12312     12    
3.131233    33
4.12312     12

In Excel 2 (different workbook), we have the same layout, however there would be single entries for each ticket and the hours summed up corresponding to each ticket.

ticket      hours
---------+-------
1.12312     24  
2.131233    77

the formula that I have started with to sync any changes in excel1 to excel 2 is

=[Source.xlsx]Sheet1!$B:B

where source.xlsx is the excel1 But, obviously this does not do any summation, just syncs data, I haven't been able to suitably modify this to fit my requirement.

EDIT

Based on the given link I edited the formula

PFB the source and destination

enter image description here

destination with the new formula looks like below

enter image description here

the formula used is:

=SUMIFS('D:\Users\...\Desktop\POC - files\[Source.xlsx]Sheet1'!$B:B,'D:\Users\...\Desktop\POC - files\[Source.xlsx]Sheet1'!$A:A, B2)

Now this does not work, probably because of some silly issue, would be great if someone pointed out!

EDIT

The below given formula does not work... enter image description here

Also , this would do it only for one ticket, not for all tickets in the column A

EDIT

Formula used

=SUMIFS('C:\Users\kphil\Desktop\POC\[source.xlsx]Sheet1'!$B$2:$B$65535,'C:\Users\kphil\Desktop\POC\[source.xlsx]Sheet1'!$A$2:$A$65535, A2)

enter image description here

Upvotes: 0

Views: 53

Answers (2)

Jonathan
Jonathan

Reputation: 663

In your formula it should be A2 instead of B2 in Criteria1. The second formula is a sum product which will work when the workbook is closed.

=SUMIFS('D:\Users\...\Desktop\POC - files\[Source.xlsx]Sheet1'!$B$2:$B$65535,'D:\Users\...\Desktop\POC - files\[Source.xlsx]Sheet1'!$A$2:$A$65535, A2)


=SUMPRODUCT(('C:\Users\kphil\Desktop\POC\[source.xlsx]Sheet1'!$A$2:$A$65535=A2)*('C:\Users\kphil\Desktop\POC\[source.xlsx]Sheet1'!$B$2:$B$65535))

Upvotes: 1

M.L
M.L

Reputation: 328

You can also use Pivot - it is very easy once you get the hang of it. If you want to average your no of hours as against say sum, that can be done as well. (Or max/min/stdDev/etc) Here's a link that explains how. Once you are comfortable with playing around the labels, you'll not need to enter formulae ever again.

PS: would have said all this in a comment, but don't have the reputation (sorry!)

Upvotes: 0

Related Questions