Nested Formula

[Jeannette]

How can I do the following formula: I want to pull out
the person's initial(column CM)then give me the quarter
months 1,2,3=1Q; 4,5,6=2Q; 7,8,9=3Q; 10,11,12=4Q (column
V) for that person then give me the sum of column AJ.
I'm trying to breakdown a figure (column AJ) by quarters.

Any assistance would be greatly appreciated!!

Column AJ: Column V: Column CM:
$6,200,395.00 3 RD
$456,994.00 7 KT
$9,763,383.00 1 LR
$8,451,888.00 4 RD
$8,789,548.00 5 KT

 

[N Harkawat]

One way using a helper column:-
Insert a Column next to Column V and type the following formula and copy
down
=CHOOSE(V1,"q1","q1","q1","q2","q2","q2","q3","q3","q3","q4","q4","q4")
Then sort it by Column CM and then by column W
Go to data subtotal and do a sum first by Column CM and then again by
column W (during second subtotal remove the replace current .. check box)

 

[Dana DeLouis]

One possible formula for "quarter" might be the following assuming A1 has
the number 1-12.
=INT((A1-1)/3)+1 & " Q"

I would recommend a Pivot table, and insert the names and quarter field into
the Row area of the pivot table.
Another option would be to not insert a "quarter" field, and just construct
your pivot table with Name and month number.
Then "Group" your month numbers in groups of 3 (1-3, 4-6...etc)

HTH

 

[Tim C]

I would name an appropriate portion of columns AJ, V, and CM as Amount,
Month and Initials.
Then in a second sheet, I would put the initials in column A and quarters in
row 1.

Then use this formula in B2 and copy it down and accross.

=SUMPRODUCT((Initials=$A2)*(INT((Month-1)/3)+1=B$1)*(Amount))

Resulting in:

1 2 3 4
RD 6,200,395 8,451,888 0 0
KT 0 8,789,548 456,994 0
LR 9,763,383 0 0 0

Tim C