Id like to filter out and total som

[bthomp]

Id like to filter out and total some numbers based on the month a user selects.

I have a dropdown containing the 12 mths from which the user will pick.

In the adjacent cell I have the formula below. B5:100 contains dates in dd/mm/yy format and C5:C100 contains numbers. The "" part of that is where I need to input the month the user selects from the dropdown.

=SUMIF(B5:B100,"",C5:C100)

Does anyone know how I can do it ?

Many thanks,

Ben.

 

[xlbo]

depends how your "Month" is displayed - Jan / Feb / Mar or 1/2/3 - say your dropdown is in A1

=sumproduct((text(B5:B100,"mmm&quot=A1)*(C5:C100)
for Jan / Feb etc
=SUMPRODUCT((month(B5:B100)=A1)*(C5:C100))
for 1 /2 etc


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

Hi,

2 parts to this problem

1. You can autofilter or advance by 2 criteria for date

For instance to select everyting for Oct 2003...

DATE         DATE
>=10/1/2003  <=10/31/2003

Alternatively, you could format the date column for a mmm-yyyy or similar format and then simply select the desired value.
2. Using the SUBTOTAL function will summ only visible cells.

=SUBTOTAL(9,C5:C100)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com