Sum numbers in a column by corresponding number

Closed
denman Posts 2 Registration date Tuesday April 30, 2013 Status Member Last seen April 30, 2013 - Apr 30, 2013 at 03:50 AM
sgmpatnaik Posts 54 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 - Apr 30, 2013 at 09:45 AM
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

sgmpatnaik Posts 54 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 45
Apr 30, 2013 at 06:55 AM
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
0
denman Posts 2 Registration date Tuesday April 30, 2013 Status Member Last seen April 30, 2013
Apr 30, 2013 at 09:26 AM
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 :)
0
sgmpatnaik Posts 54 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 45
Apr 30, 2013 at 09:45 AM
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
0