That's amazing, thank you so much. You saved me tons of extra work. I'm now
convinced Excel can do anything.

"T. Valko" wrote:

=SUMPRODUCT(--(A2:A12="name1"),--(B2:B12=DATE(2009,7,1)))*NP+SUMPRODUCT(--(A2:A12="name1"),--(B2:B12<DATE(2009,7,1)))*OP

You can shorten that a bit by using cells to hold the criteria:

E2 = some name
F2 = 7/1/2009

=SUMPRODUCT(((A2:A12=E2)*((B2:B12=F2)*NP))+((A2:A 12=E2)*((B2:B12<F2)*OP)))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Okay, I was able to figure this one out by using:
=(COUNTIF(B:B,"7/1/09")*[new price])+(COUNTIF(B:B,"<7/1/09")*[old price])

Ok, there's only one problem with your formula...

You're completely excluding 7/1/2009. Try your formula on this data.

6/28/2009...5
6/29/2009...5
6/30/2009...5
7/1/2009...10
7/2/2009...10

So, if the new price takes effect 7/1/2009 then try it like this:

=COUNTIF(B:B,"=7/1/09")*NP+COUNTIF(B:B,"<7/1/09")*OP

For your new problem....

=SUMPRODUCT(--(A2:A12="name1"),--(B2:B12=DATE(2009,7,1)))*NP+SUMPRODUCT(--(A2:A12="name1"),--(B2:B12<DATE(2009,7,1)))*OP

Note that with SUMPRODUCT you *can't* use entire columns as range
references unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"jmj713" wrote in message
...
Okay, I was able to figure this one out by using:

=(COUNTIF(B:B,"7/1/09")*[new price])+(COUNTIF(B:B,"<7/1/09")*[old
price])

However, I'm stumped at another portion of this spreadsheet which I
didn't
mention, because it didn't occur to me right away.

Parallel to these dates in B I have names in A. They are repeating and so
in
another section there is a table counting balances for each name. I can't
think of a way to implement this date-specific counting and multiplying
formula and take into account specific names.

The total should take into account that if Bob was served on 6/30/09 he
was
charged, say, $1, but when Bob was served on 7/2/09, he was charged $2.

I'm totally stumped. Is this even possible?