Count total balance using two numbers
 

This may be a little confusing, please bear with me.

I need to calculate a total balance, using a column of service dates. The
price per date is the same. This is simple. Problem is, since July 2009 I
need to use a different rate, while retaining the pre-July rate as well in
the total. So is there a way to get a total balance for a column of dates
with two different multipliers?

I was using a simple COUNTA formula for the total number of dates and then
just multiplying that number by my amount. I'm thinking to combine two
counting formulas, one looking for pre-July 2009 dates and multiplying those
by the old price and the second looking for the post-July 2009 dates and
multiplying them by the new price. But I haven't been able to come up with
this yet...

Any help is appreciated.

Count total balance using two numbers
 

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?

Count total balance using two numbers
 

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?

Count total balance using two numbers
 

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

Count total balance using two numbers
 

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?

Count total balance using two numbers
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jmj713" wrote in message
...
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?