You don't need a macro a formula will work

On sheet 1 - Header Row 1, data starts in row 2
Column A - Year
Column B - Quarter (Q1, q2, q3, q4)
Column C - Probability
column D - amount


on Sheet 2

Column A Column B
Row 1 Enter Year 2002
Row 2 Enter Quarter Q2
Row 3 Min Probaility 90
row 4 Max Probability 100

Note: If Probability is less than 50 then enter 0 as min and 50 as max


=sumproduct(--(B1=sheet1!A2:A100),--(B2=sheet1!B2:B100),--(B3<=sheet1!C2:C100),--(B4=sheet1!C2:C100),D2:D100)


"kay" wrote:

Hi
I need help writing code in VB to run macro.
I have a EXCEL spreadsheet which contains Year,Quarter, Probability and
amount column.
in year column i have years entry 2001,2002...
in quarter column i have quarter entry Q1,Q2,...
ein Probability column I have prob in percentage 10%,20%,30%...
in Amount i have salary value ... $10000,$50000
Now i am writing macro to sum amount based on user entry

For example in other spread sheet of same excel book user will enter
Enter Year: /or Select from the Drop Down List(2000,2001,...)
Enter Quarter /or Select From the Drop Down List (Q1,Q2,Q3,Q4)
Enter Prob: =90 or between =50 and <=80

After entering this data , it should sum the amount (salary value).
based on this selection i will have four tables for each quarter
For example

Q1 - $xxxxxx (sum of the amount)
Q2 - $yyyyyy (sum of the amount)
Q3 - $jjjjkkkkk (sum of the amount)
Q4 - $eeeeee (sum of the amount)

and if possible it should create chart ( bar chart to show amount for each
quarter)

I am trying to write a code but stuck many places.
can anyone help me to write code.

Thanks!
Kay