Trying use vlookup and countif

[Dhurl]

Hi

I'm looking for help on isolating and breaking down some figures

Customer Type Payment Credit Limit Credit Check Total Amount Difference
Bus test 1 Direct Debit 90 <=100 80 10
Business Cash 90 <=100 200 -110
Bus 3 Direct Debit 80 <=100 20 60
Bus test 1 Direct Debit 20 <=100 120 -100
Bus test 1 Cash 20 <=100 10 10
Business Direct Debit 40 <=100 45 -5

I want to isolate 'Bus test 1' and count the average 'total amount' and total 'total amounts'

Can anyone help with this please?

 

[Dhurl]

sorry table should be clearer

Customer Type __________ Payment ___________Credit Limit__________ Credit Check_______ Total Amount __________ Difference
Bus test 1______________ Direct Debit _____________90 __________________<=100 ____________80 __________________10
Business ______________ Cash__________________90 __________________<=100 ____________200__________________-110
Bus 3______________ ____Direct Debit _____________80 __________________<=100 ____________20 __________________60
Bus test 1______________ ____Direct Debit _________20 __________________<=100 ____________120 __________________-100
Bus test 1______________ Cash__________________20 __________________<=100 ____________10__________________10
Business______________ ____Direct Debit _________40 __________________<=100 ____________45 __________________-5

 

[AlanY]

try

Excel 2012
	A	B	C	D	E	F
1	Customer	Type Payment	Credit Limit	Credit Check	Total Amount	Difference
2	Bus test 1	Direct Debit	90	<=100	80	10
3	Business	Cash	90	<=100	200	-110
4	Bus 3	Direct Debit	80	<=100	20	60
5	Bus test 1	Direct Debit	20	<=100	120	-100
6	Bus test 1	Cash	20	<=100	10	10
7	Business	Direct Debi	t 40	<=100	45	-5
8
9	Bus test 1
10	Total Amount	210
11	Average	70
Sheet1

Cell Formulas
Range	Formula
B10	=SUMIF(A2:A7,A9,E2:E7)
B11	=AVERAGEIF(A2:A7,A9,E2:E7)

 

[pjmorris]

Hi Dhurl,

Welcome to the forum.

Your original post isn't well laid out so deciding which column is which is difficult, but assuming I've interpreted it correctly then Customer is in Column A, and Total Amount is in column E. If this is correct then:

To calculate the Total Amount: =SUMIF(A2:A7,"Bus Test 1",E2:E7)
To calculate the average Total Amount: =AVERAGEIF(A2:A7,"Bus Test 1",E2:E7)

Hope this helps.

Regards

 

[pjmorris]

Alan, how do you insert tables as you've done above?

Regards

 

[AlanY]

Alan, how do you insert tables as you've done above?

Regards

the HTMLmaker addon from the forum,

http://www.mrexcel.com/htmlmaker.html

 

[Dhurl]

Hi AlanY

Thanks for the above.

I'm wondering how I can do this on a much bigger scale however.

So basically trying to find the above but isolate one customers numbers within a few thousand rows?

Thanks

 

[AlanY]

shouldn't be any problem by increase the ranges on Cols A and E to cover all the rows

 

[Dhurl]

shouldn't be any problem by increase the ranges on Cols A and E to cover all the rows

Thanks

 

[AlanY]

you're welcome