Count total balance using two numbers

[jmj713]

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.

 

[jmj713]

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?

 

[T. Valko]

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

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?

 

[T. Valko]

=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:A12=E2)*((B2:B12<F2)*OP)))


--
Biff
Microsoft Excel MVP

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

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?

 

[jmj713]

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

=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:A12=E2)*((B2:B12<F2)*OP)))


--
Biff
Microsoft Excel MVP

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

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?

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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

=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:A12=E2)*((B2:B12<F2)*OP)))


--
Biff
Microsoft Excel MVP

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


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?