Thread: Hope this makes sense...

Here is the Data I have...

Last_Name, First_Name, Balance, Due_Date
Rich, Joseph, $8000.00, 7/15/2006
Amar, Jane, $6500.00, 3/25/2005
Stine, Richard, $7500.00, 9/15/2006

The first line is the title for the excel cells.

How do I word the formula bar if I would like to get a total balance from accounts that are due from 3/15/05-7/15/2006 and that fall within the alpha of A-K?

Your alpha is not clear, if you mean last name starts with a A to K

then

=SUMPRODUCT((LEFT(A2:A41,1)<="k")*((LEFT(A2:A41,1)>="a")*(d2:d41<=f1)*(d2:d41>=f2)*(C2:C41))

where f1 = 7/15/2006 if it id a date and not text
f2=3/15/05

regards

Dav

well the dates in your sheet are not trully dates, if you format them to a number format do they change or stay the same. If they are text, it would be better to change the spreadsheet, but if you wish i can come up with a formula

Regards

dav

Originally Posted by Dav

well the dates in your sheet are not trully dates, if you format them to a number format do they change or stay the same. If they are text, it would be better to change the spreadsheet, but if you wish i can come up with a formula

Regards

dav

If I format the text to date it changes from 7/15/2006 to 7/15/06. What would you suggest?

Here is the sample sheet i have been working on, maybe you can get a better view at this...

Are you using 5/01/06 - 10/01/06 as dates or 15/07/2006 -
25/03/2005 ??

How do you get 3 accounts and $18,500

VBA Noob

Originally Posted by VBA Noob

Are you using 5/01/06 - 10/01/06 as dates or 15/07/2006 -
25/03/2005 ??

How do you get 3 accounts and $18,500

VBA Noob

I'm using 5/01/06 - 10/01/06.

Based on the following criteria I get 3 accounts...

Any accounts due from 05/01/06 thru 10/01/06 and the first letter of their last name is from A - K.

?? Which three accounts fall within 5 days

Give more details.

VBA Noob

I see...

Actually, the dates mean, May 1st 2006 thru October 1st 2006. Sorry about that.

Hi,

These work now using Dav's formulas. Just the criteria dates where wrong way around compared to your spreadsheet.

A to K

Returns $18000

=SUMPRODUCT((LEFT(A2:A40,1)<="k")*((LEFT(A2:A40,1)>="a")*(D2:D40<=F1)*(D2:D40>=F2)*(C2:C40)))

Returns 3
=SUMPRODUCT((LEFT(A2:A40,1)<="k")*((LEFT(A2:A40,1)>="a")*(D2:D40<=F1)*(D2:D40>=F2)))

L to Z
Returns $17000 not $32000 as Rodriguez falls in April 06

=SUMPRODUCT((LEFT(A2:A40,1)<="Z")*((LEFT(A2:A40,1)>="L")*(D2:D40<=F1)*(D2:D40>=F2)*(C2:C40)))

Returns 3 for the same reason above

=SUMPRODUCT((LEFT(A2:A40,1)<="Z")*((LEFT(A2:A40,1)>="L")*(D2:D40<=F1)*(D2:D40>=F2)))

Good job Dav.

VBA Noob