Dynamic Addition with logic statement

[dgranruth]

Background: I'm trying to help some of my associates keep track of a particular account. There are four primary types of customer interactions they can perform (Activations, Upgrades, Accessory, Maintenance). What I would like to be able to do is have the Consultant choose their name, enter in information for the transaction (Date, Trans #, Notes) and select the type of interaction (listed above) and have the totals dynamically list near the top of the spread sheet.

Column A starting at Cell 15 down is a dropdown list pulling the names of the current consultants, list located (J6 to J11).
Column D starting at Cell 15 down is a dropdown list pulling the type of transactions from list located (K6 to K9).

Basically, at Cell B7 it needs to be able to do something like (if A15 down is Terrence AND D15 down is Activation, then add totals to B7)
So if it counts there are 22 total transactions in which the consultant was Terrence AND the transaction was an Activation then B7 would display the total 22.

Basically the same goes for the other consultants AND variable types logically. The only oddball would be Accessories, in which case it wouldn't display the number of lines in which the Consultant AND Accessory agreed, but in that case take that line's TOTAL amount and put it in Dollar Values for that Cell.

If I'm not asking the question properly please let me know.

 

[Akashwani]

Hi and welcome to MrExcel.

It would be helpful if you can post some sample data using the link in my signature.

My sample data.....

Excel Workbook
	A	B	C	D	E	F
1	Date	Trans #	Consultant	Type	Amount
2	01/01/2013	AA10000	Bart Simpson	Activations	300.00
3	02/01/2013	AA10001	Lisa Simpson	Upgrades	200.00
4	03/01/2013	AA10002	Homer Simpson	Accessory	400.00
5	04/01/2013	AA10003	Bart Simpson	Maintenance	163.00
6	05/01/2013	AA10004	Bart Simpson	Activations	300.00
7	01/01/2013	AA10005	Homer Simpson	Activations	300.00
8	07/01/2013	AA10006	Bart Simpson	Upgrades	400.00
9	08/01/2013	AA10007	Bart Simpson	Accessory	300.00
10	09/01/2013	AA10008	Homer Simpson	Accessory	300.00
11	10/01/2013	AA10009	Lisa Simpson	Upgrades	300.00
12	01/01/2013	AA10010	Homer Simpson	Upgrades	100.00
13	12/01/2013	AA10011	Bart Simpson	Maintenance	100.00
14	01/01/2013	AA10012	Bart Simpson	Accessory	100.00
15	04/01/2013	AA10013	Homer Simpson	Upgrades	200.00
16	05/01/2013	AA10014	Bart Simpson	Accessory	450.00
17	06/01/2013	AA10015	Bart Simpson	Accessory	400.00
18	02/01/2013	AA10016	Bart Simpson	Activations	300.00
19	09/01/2013	AA10017	Homer Simpson	Activations	450.00
20	01/01/2013	AA10018	Bart Simpson	Upgrades	400.00
21
Sheet5

My example results.....

Excel Workbook
	A	B	C	D	E
5
6		Result
7		3
8
9
10
11
12
13
14	Consultant			Transaction
15	Bart Simpson			Activations
16
Sheet4

Or.....

Excel Workbook
	A	B	C	D	E
5
6		Result
7		1250
8
9
10
11
12
13
14	Consultant			Transaction
15	Bart Simpson			Accessory
16
Sheet4

I don't know how to convert the result in B7 to a dollar amount without it being displayed as TEXT and not a true number.

I hope this helps you to understand what you need to post so that you get the solution you require.

Ak