Sum of cells in several rows

[ddsunil]

Hello Everyone
A problem I am sure only you gurus here can solve.

I have a sheet with three columns.
Studentid, Billing Amount Payment Amount

There are about 15-20 rows. As an example consider these as the rows.
A B B
Jim 100 50
John 100 80
Sam 100 100
Jill 100 100
Jim 100 50
John 100 20

The two rows with name Jim belong to one person. I need to add a new column with an if condition in the cells of that column. The condition is if(B2=B3,1,0) Now B2 and B3 will not be the same for Jim and John as they have not paid the full amount. But in reality they have as their rows are at the end where they have paid the balance amount. So I need to make sure that Jim and John are also assigned the flag with value 1 because they have totally paid the amount in two installments.

Thanks so much in advance for the help

S

 

[DonkeyOte]

If you're saying that the Billing amounts are unique only by individual (ie Jim has one Billing amount - 100) then:

Excel Workbook
	A	B	C	D
1		Billing	Payment
2	Jim	100	50	1
3	John	100	80	1
4	Sam	100	100	1
5	Jill	100	100	1
6	Jim	100	50	1
7	John	100	20	1
Sheet1

If you have large data sets you would be best served sorting your data by Name first.

You could also look to use a Pivot Table - with Name as Row Field, Billing as Data Field set to Average and Payment as Data Field set to SUM .. this would give you a quick view.

 

[ddsunil]

Thank you so much.

Sunny

 

[ddsunil]

Extension to my earlier post.
Column B is ID
Column J is BillingAmount
Column L is collection Amount
Column N is Payment Mode
Column AI is a Flag set to true or false based on a formula and the formula is what I am looking for help on.

sample Data
B J L N AI
1 100 20 Credit Card
2 200 100 Cash
1 100 50 Coupon
1 100 30 Cash
2 200 100 Cash

I want to set the column AI to true or false based on the following conditions.

If Sum of collection Amount is equal to the billing Amount (Reply to my original post helped me solve this)
AND
If Payment Mode is not equal to Coupon (This is where I am lost)

If you see the sample data, ID 1 has made three payments. One with Credit card, one with coupon and one with cash. Now the value of all cells in column AI for ID 1 should be False because one of the payment mode is coupon. For ID 2, the value should be true for all cells in AI because NONE of the payment mode is Coupon. So basically, if multiple payments are made and at least one of the payment is made by coupon then all the cells in column AI for that ID should be set to false.

Hope I provided clarity on the issue I am facing. Thanks so much for your help.

Sunny

 

[yogeshmaney]

https://rapidshare.com/files/3807305256/ddsunil.xls

 

[ddsunil]

Dear Yogesh
Can you email the file to my email address or just post the formula here? I am unable to open the site. McAfee site advisor keeps blocking it and when I click on Visit anyway, it just gets stuck. Tried this on all the browsers. IE, Firefox, Tor, and Chrome.

Thanks
Sunny

 

[ddsunil]

Yogesh
Never mind. I was able to get it from another computer. Thanks so much for the help

Sunny

 

[ddsunil]

Yogesh
When I copied and pasted the formulae in my sheet I get all values False. How do I retain the '{' and '}'. I am losing them and that is what I think is causing the formula to fail.

thanks
Sunny

 

[yogeshmaney]

its array formula to enter use

Ctrl + Shift + Enter