I don't know what formula to use.

[omard.]

I have made a couple of searches, but since I don't know exactly what formula to use I'm a little lost when I do my search. I have a list that I will add to as time goes on. It will have peoples names, banks names, dollar amounts, company name, etc. I have to pull the names from the main sheet and put them on other sheets. Example, on my main sheet I have


a b c d
1 name bank amount source
2 john abc 120 adl
3
4
5

this list will have other names, banks, amounts, sources, and What I need to do is take all of the same names and add up their dollar amounts and place them in another sheet. I also need to be able to populate the name from this sheet to the other sheet. example if I add matt I want it to automatically insert that name into the other sheet and add up all his amounts. Sorry if I'm doing a horrible job of explaining. Ask anything you want. Thanks

 

[seenfresh]

Aladin

Aladin: I have a question with your formula perhaps you may be able to help me

=IF(ISNUMBER(MATCH(A3,$A$2:A2,0)),"",LOOKUP(9.99999999999999E+307,$E$1:E2)+1)

IF(logical_test,value_if_true,value_if_false)

IF the value is TRUE the closing Parenthesis "" what does this represent? I know if the value if false argument you include "" it will return the value 0 as long as there is a comma after the value if true argument.

To be sure of what I am saying:

ISNUMBER(MATCH(A3,$A$2:A2,0)=Logical_Test
"" = Value_if_True
LOOKUP(9.99999999999999E+307,$E$1:E2)+1)= Value_if_false

are these represented correctly and if so my confusion lies with the "" argument

also the Value_if_False argument refers to a BIGNUM argument right?

Thanks for your patience Any advice would make my day

Thanks Aladin

Sean

 

[Aladin Akyurek]

Re: Aladin

Aladin: I have a question with your formula perhaps you may be able to help me

=IF(ISNUMBER(MATCH(A3,$A$2:A2,0)),"",LOOKUP(9.99999999999999E+307,$E$1:E2)+1)

IF(logical_test,value_if_true,value_if_false)

IF the value is TRUE the closing Parenthesis "" what does this represent? I know if the value if false argument you include "" it will return the value 0 as long as there is a comma after the value if true argument.

To be sure of what I am saying:

ISNUMBER(MATCH(A3,$A$2:A2,0)=Logical_Test
"" = Value_if_True
LOOKUP(9.99999999999999E+307,$E$1:E2)+1)= Value_if_false

are these represented correctly and if so my confusion lies with the "" argument

also the Value_if_False argument refers to a BIGNUM argument right?

Thanks for your patience Any advice would make my day

Thanks Aladin

Sean

=IF(ISNUMBER(MATCH(A3,$A$2:A2,0)),
      "",
      LOOKUP(9.99999999999999E+307,$E$1:E2)+1)

Reads:

If there is a match for A3 in $A$2:A2 (TRUE evaluation of the condition), return "" (a blank), otherwise (FALSE evaluation of the condition) augment the last numeric value from $E$1:E2 with 1.

What the formula effects is to number/index sequentially every first/single occurrence of an item in A3:A7.

 

[seenfresh]

Thanks Aladin

You are the man!

 

[omard.]

Aladin it works great thank you very much.

 

[omard.]

Aladin, can you tell me how i can sum up the loan amounts that did not come from a specific person.

Example in your sheet Carl did 2 loans I need to add up all the other loan he did not do.

Also I need to be able to sum up the loan amounts if there is a name in another field

Example there is another field called lo. I need to be able to sum up all the loan amounts that carl did that have a name in that field, and also sum the one that do not have a name in that lo field.

Thanks

 

[Aladin Akyurek]

Aladin, can you tell me how i can sum up the loan amounts that did not come from a specific person.

Example in your sheet Carl did 2 loans I need to add up all the other loan he did not do.

Do you mean...

I3, copied down:

=IF(G3<>"",SUMIF($A$3:$A$7,"<>"&G3,$C$3:$C$7),"")

Also I need to be able to sum up the loan amounts if there is a name in another field

Example there is another field called lo. I need to be able to sum up all the loan amounts that carl did that have a name in that field, and also sum the one that do not have a name in that lo field.

Thanks

Care to post a 5-row sample along with the desired outcome(s)?

 

[omard.]

names banks source lo cu loan$
jim abc adl joe 123 50
tod 123 hdl tim abc 100
jim abc adl abc 120


the different things

1. I want to be able to count all the source that do not contain a certain name. Example in the source colum i want to add the ones that do not have adl in them. answer would be 1

2. similar to the one above but this time i want to add the ones that contain a certain name. Example in the source colum i want it to add all the ones that say adl answer is 2

3. I want to add all of jim's loan amounts that contain a name in the lo colum. Example jim has 50 of loan amounts with a name in the lo field.

4. i want to add all of jim's laon amounts that contain no name in the lo field colum. Example jim has 120 of loan amounts with no name in the lo field

 

[Aladin Akyurek]

names banks source lo cu loan$
jim abc adl joe 123 50
tod 123 hdl tim abc 100
jim abc adl abc 120


the different things

1. I want to be able to count all the source that do not contain a certain name. Example in the source colum i want to add the ones that do not have adl in them. answer would be 1

2. similar to the one above but this time i want to add the ones that contain a certain name. Example in the source colum i want it to add all the ones that say adl answer is 2

3. I want to add all of jim's loan amounts that contain a name in the lo colum. Example jim has 50 of loan amounts with a name in the lo field.

4. i want to add all of jim's laon amounts that contain no name in the lo field colum. Example jim has 120 of loan amounts with no name in the lo field

Book6
	A	B	C	D	E	F	G	H	I	J
1	names	banks	source	lo	cu	loan$
2	jim	abc	adl	joe	123	50		1)	adl	1
3	tod	123	hdl	tim	abc	100		2)	adl	2
4	jim	abc	adl		abc	120		3)	jim	50
5								4)	jim	120
6
7
Sheet1

1)

=COUNTIF($C$2:$C$4,"?*")-COUNTIF($C$2:$C$4,I2)

2)

=COUNTIF($C$2:$C$4,I3)

3)

=SUMPRODUCT(--($A$2:$A$4=I4),--($D$2:$D$4<>""),$F$2:$F$4)

4)

=SUMPRODUCT(--($A$2:$A$4=I4),--($D$2:$D$4=""),$F$2:$F$4)

 

[omard.]

Thank you very much.

 

[omard.]

I have 2 more questions if you don't mind.

1. When I use this formula =IF(ROWS($G$3:G3)<=$G$1,LOOKUP(ROWS($G$3:G3),$E$3:$E$7,$A$3:$A$7),"") it will skip a box on my destination page if iskip a box on my source page. How can I solve this.

2. I want to be able to add up the name that use a bank. example how many times did john use abc bank. amro, city bank, etc