sumproduct by quarter

[Brian]

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$2>3,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]

=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]

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....

 

[T. Valko]

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)

 

[T. Valko]

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)