Sum numbers in a column by corresponding number

Closed
Report
Posts
2
Registration date
Tuesday April 30, 2013
Status
Member
Last seen
April 30, 2013
-
Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
-
Hi there

My question basically boils down to payment / invoice match.
In one column I have amounts (paid or invoice - often one payment will include more invoices) on the other column I have the number which links invoices to payment and it should equal zero.
Example

Column N Column P
843.43- 17002179
3872.46- 17002179
905.69- 17002179
5624.55 17002179

I'd like to sum amounts in N when they have the same reference cell on P. I've tried the sumif perhaps not well because there are too many variables in P.
Thank you in advance for you help
Marco

2 replies

Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
44
Hi

Please Try the below method

1. Say your Data is stored in Column N and P, in the Column N you are enter the amounts and In Column P you are enter the Invoice Nos. so you want to know the total amount against the Invoice No. so try this

Suppose your data range is N1:P1000 and you want the figure in the Column S

S2: =SUMIFS($N$2:$N$1000,$P$2:$P$1000,"INVOICE NO")

EX. S2: =SUMIFS($N$2:$N$1000,$P$2:$P$1000,"17002179")

2. Say your Data is stored in Column N and P, in the Column N you are enter the amounts and In Column P you are enter the Invoice Nos. and In Another Column Say Column R has the Invoice Nos. which invoice are mention in the Column P

Try This in S2

S2: =SUMIFS($N$2:$N$1000,$P$2:$P$1000,$R2)


Hope it will clear your problem other wise try to upload a sample file with manual result from the given below link

https://authentification.site

Thanks

Patnaik
Posts
2
Registration date
Tuesday April 30, 2013
Status
Member
Last seen
April 30, 2013

Hi there
thank you for the quick response, I tried both formulas without success, so I have uploaded the partial file to yr link.
The prob is I need excel to find the variable IF, because there are so many it would be impossible to enter them all manually
Cheers :)
Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
44
Hi

you no need to give the full details to us just share some details to better understanding your problem

so in which sheet you want to the formula just copy the sheet and past in new sheet and send us

other wise please give some more information actually what do you want

Thanks

Patnaik