| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need a total count for various numbers in a column | Excel Worksheet Functions | |||
| How to display running-down numbers with total count? | Excel Worksheet Functions | |||
| Find the balance of amount from a total count of 60.... | Charts and Charting in Excel | |||
| How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
| Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
Linear Mode
