SUM

[niting]

Hie forum,


I have a sheet wid vendors name and excise duty @ 10.3% against dere name. I have andr sheet wid document no. as one col and the ED broken down into its constituen part, i.e. 10, 2 and 1%.


Since there is no common data linking the sheets, I was thinking of trying to sum the second sheet as per its document numbers and then use count if to reconcile the ED. Is there a sum function which I can use to sum the constituent of ED against different document number in the sheet. the data is sumthin like dis-:


DOC No. AMNT

121 100

121 2

121 1

151 200

151 4

151 2


Since, there are lot of doc numbers in the sheet, could you please help me with an efficeint way to acheive the result???


Thanks

Nitin

 

[Luke M]

For the SUM of 121 in your example, are you wanting an answer of 103, or 3?


Former:

=SUMIF(A:A,121)


Latter:

=SUMIF(A:A,121)-LOOKUP(121,A:B)

 

[niting]

Luke,


I have some hundreds of the document numbers and what I am looking for is , if there is a way I can sum all the different documents without having to manually or piecemeal summing them using filters.


Thanks

Nitin

 

[Luke M]

Assuming the answer to my earlier question is 103, the fastest way would be to select columns A:B, and then build a PivotTable with Documents in the Row Area, and put a Sum of AMNT in the Data area.

 

[charlesalvear]

PivotTable is the way to go. You can then get Counts and/or Sums grouped by the document numbers fairly seamlessly.

 

[niting]

Thanks a lot guys!!