sumproduct by quarter
Ooops! I forgot to reference the currency cell:
=SUMPRODUCT(--(CEILING(A2:A5/3,1)=CEILING(MONTH("1-"&E1)/3,1)),--(B2:B5=F1),C2:C5)
--
Biff
Microsoft Excel MVP
"T. Valko" wrote in message
...
Maybe this:
E1 = user entered month = April
F1 = JPY
=SUMPRODUCT(--(CEILING(A2:A5/3,1)=CEILING(MONTH("1-"&E1)/3,1)),--(B2:B5="JPY"),C2:C5)
--
Biff
Microsoft Excel MVP
"Brian" wrote in message
...
Dave,
Thanks...but this doesn't appear to do what I need it to...
I want it to add the data in seperate columns depending on what quarter
it
occured in. If it's in the first qtr...I want it only to add month
1-3...but
I want to give the person viewing the report the ability to choose the
specific month.
For example if the person only selects the month of Feb...I only want it
to
add together months 1&2...etc....
"Dave Peterson" wrote:
=SUMPRODUCT(--(INT((A2:A99-1)/3)=0),--(B2:B99="jpy"),C2:C99)
That 0 (in the first portion of the formula) represents quarter 1.
If you wanted the 4th quarter, then use =3.
Brian wrote:
On one tab I have the following data:
Month Currency Amount
1 JPY 50.00
3 GBP 75.00
4 JPY 50.00
7 GBP 35.00
On another tab, I've setup the Sumproduct formula to give me the
amounts
that correspond with the correct currency, but how can I get a total
by
quarter?
For example, on this other tab, I'd like to allow the user to select a
month, then have the spreadsheet autopopulate the quarterly info
to-date
based on the month they selected...
So if the user selects the month April and JPY...in the first column
it will
add together any JPY amount with a month less than or equal to March
(3)...and it column two it will add together any amounts with a month
of
April (4)...and disregard anything beyond that.
Any ideas how I can do this? I can get the first QTR column to work
with
the following formula...
=(IF($A$23,SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=3)*('Data 1st
Half'!$E$4:$E$65000="JPY")*('Data 1st
Half'!$G$4:$G$65000)),SUMPRODUCT(('Data
1st Half'!$B$4:$B$65000<=$A$2)*('Data 1st
Half'!$E$4:$E$65000="JPY")*('Data
1st Half'!$G$4:$G$65000)))
But can't get this work for the periods 4, 5 & 6. Any ideas?
--
Dave Peterson
|