Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
Hi,
I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
Look for week numbers greate than X and less then Y. Don't use dates. The
formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
Hi Joel,
Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
You will need to convert the date to a week number. There are plenty of
request at this website for conversion from a date to a week number (do a search). I can help with the conversion but not sure how your week numbers work. do they start on the first sunday of the month or start on saturday. Week numbers vary from company to company. The function WeekNum() may work. "Owl" wrote: Hi Joel, Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
Hi Joel,
I finally converted the weeks to week number and am now trying to link this to my other file Week Number ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 So, for example, how would i sum up weeks 2 and 3 only? Would it be sumif on week 3 less sumif week 1. And if i used the < formula, how would i ensure i dont double count if its for week 2 only? "Joel" wrote: You will need to convert the date to a week number. There are plenty of request at this website for conversion from a date to a week number (do a search). I can help with the conversion but not sure how your week numbers work. do they start on the first sunday of the month or start on saturday. Week numbers vary from company to company. The function WeekNum() may work. "Owl" wrote: Hi Joel, Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
I would sum each column (my example in row 25) and use sumproduct. If you
have 52 weeks which would be columns B to BZ. Then the following formula can be used =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B25:BZ25) "Owl" wrote: Hi Joel, I finally converted the weeks to week number and am now trying to link this to my other file Week Number ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 So, for example, how would i sum up weeks 2 and 3 only? Would it be sumif on week 3 less sumif week 1. And if i used the < formula, how would i ensure i dont double count if its for week 2 only? "Joel" wrote: You will need to convert the date to a week number. There are plenty of request at this website for conversion from a date to a week number (do a search). I can help with the conversion but not sure how your week numbers work. do they start on the first sunday of the month or start on saturday. Week numbers vary from company to company. The function WeekNum() may work. "Owl" wrote: Hi Joel, Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
Thanks Joel - youve been a great help! Just needed one more small thing -
ive amended the =2 to =cell reference in my schedule. Is there a way of only summing certain rows eg : for weeks 2-5, i want to pull through data for a specific product (which is in rows on my data schedule) Eg : Week 2 Week 3 Sales between Weeks 3 and 2 Product number *sumproduct formulae 9781405230278 And id want data from the below table Week Numbers ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 Would it be a combination of vlookup/sumifs? "Joel" wrote: I would sum each column (my example in row 25) and use sumproduct. If you have 52 weeks which would be columns B to BZ. Then the following formula can be used =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B25:BZ25) "Owl" wrote: Hi Joel, I finally converted the weeks to week number and am now trying to link this to my other file Week Number ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 So, for example, how would i sum up weeks 2 and 3 only? Would it be sumif on week 3 less sumif week 1. And if i used the < formula, how would i ensure i dont double count if its for week 2 only? "Joel" wrote: You will need to convert the date to a week number. There are plenty of request at this website for conversion from a date to a week number (do a search). I can help with the conversion but not sure how your week numbers work. do they start on the first sunday of the month or start on saturday. Week numbers vary from company to company. The function WeekNum() may work. "Owl" wrote: Hi Joel, Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
Use sumproduct for sums at bottom of each column
=sumProduct(--($A3:$A25=9781405230278),B3:B25) Then copy the formula acroos all columns. Notice I put a dollar sign in front of the references to column A so they don't change when you copy. "Owl" wrote: Thanks Joel - youve been a great help! Just needed one more small thing - ive amended the =2 to =cell reference in my schedule. Is there a way of only summing certain rows eg : for weeks 2-5, i want to pull through data for a specific product (which is in rows on my data schedule) Eg : Week 2 Week 3 Sales between Weeks 3 and 2 Product number *sumproduct formulae 9781405230278 And id want data from the below table Week Numbers ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 Would it be a combination of vlookup/sumifs? "Joel" wrote: I would sum each column (my example in row 25) and use sumproduct. If you have 52 weeks which would be columns B to BZ. Then the following formula can be used =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B25:BZ25) "Owl" wrote: Hi Joel, I finally converted the weeks to week number and am now trying to link this to my other file Week Number ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 So, for example, how would i sum up weeks 2 and 3 only? Would it be sumif on week 3 less sumif week 1. And if i used the < formula, how would i ensure i dont double count if its for week 2 only? "Joel" wrote: You will need to convert the date to a week number. There are plenty of request at this website for conversion from a date to a week number (do a search). I can help with the conversion but not sure how your week numbers work. do they start on the first sunday of the month or start on saturday. Week numbers vary from company to company. The function WeekNum() may work. "Owl" wrote: Hi Joel, Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
Hi
That didnt work so ive decided to go back to the original data all in columns and use that. Prior to this, it was all in pivots and so confusing. It has sales data by week and by code all in columns. What i now need to do is put in a formula to capture all sales for a specific code and specific weeks - im thinking a nested sumif? unless there is another way of specifying summing only the rows which have certain weeks and certain products? "Joel" wrote: Use sumproduct for sums at bottom of each column =sumProduct(--($A3:$A25=9781405230278),B3:B25) Then copy the formula acroos all columns. Notice I put a dollar sign in front of the references to column A so they don't change when you copy. "Owl" wrote: Thanks Joel - youve been a great help! Just needed one more small thing - ive amended the =2 to =cell reference in my schedule. Is there a way of only summing certain rows eg : for weeks 2-5, i want to pull through data for a specific product (which is in rows on my data schedule) Eg : Week 2 Week 3 Sales between Weeks 3 and 2 Product number *sumproduct formulae 9781405230278 And id want data from the below table Week Numbers ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 Would it be a combination of vlookup/sumifs? "Joel" wrote: I would sum each column (my example in row 25) and use sumproduct. If you have 52 weeks which would be columns B to BZ. Then the following formula can be used =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B25:BZ25) "Owl" wrote: Hi Joel, I finally converted the weeks to week number and am now trying to link this to my other file Week Number ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 So, for example, how would i sum up weeks 2 and 3 only? Would it be sumif on week 3 less sumif week 1. And if i used the < formula, how would i ensure i dont double count if its for week 2 only? "Joel" wrote: You will need to convert the date to a week number. There are plenty of request at this website for conversion from a date to a week number (do a search). I can help with the conversion but not sure how your week numbers work. do they start on the first sunday of the month or start on saturday. Week numbers vary from company to company. The function WeekNum() may work. "Owl" wrote: Hi Joel, Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
I would still do it in 2 steps. First add each column using the following
formula Assume this formula is in cell B30. Then copy in row 30 from columns B to BZ. =sumProduct(--($A3:$A25=9781405230278),B3:B25) Then the total for week 2 - 3 would be (row one would be where your work weeks are located) =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B30:BZ30) "Owl" wrote: Hi That didnt work so ive decided to go back to the original data all in columns and use that. Prior to this, it was all in pivots and so confusing. It has sales data by week and by code all in columns. What i now need to do is put in a formula to capture all sales for a specific code and specific weeks - im thinking a nested sumif? unless there is another way of specifying summing only the rows which have certain weeks and certain products? "Joel" wrote: Use sumproduct for sums at bottom of each column =sumProduct(--($A3:$A25=9781405230278),B3:B25) Then copy the formula acroos all columns. Notice I put a dollar sign in front of the references to column A so they don't change when you copy. "Owl" wrote: Thanks Joel - youve been a great help! Just needed one more small thing - ive amended the =2 to =cell reference in my schedule. Is there a way of only summing certain rows eg : for weeks 2-5, i want to pull through data for a specific product (which is in rows on my data schedule) Eg : Week 2 Week 3 Sales between Weeks 3 and 2 Product number *sumproduct formulae 9781405230278 And id want data from the below table Week Numbers ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 Would it be a combination of vlookup/sumifs? "Joel" wrote: I would sum each column (my example in row 25) and use sumproduct. If you have 52 weeks which would be columns B to BZ. Then the following formula can be used =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B25:BZ25) "Owl" wrote: Hi Joel, I finally converted the weeks to week number and am now trying to link this to my other file Week Number ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 So, for example, how would i sum up weeks 2 and 3 only? Would it be sumif on week 3 less sumif week 1. And if i used the < formula, how would i ensure i dont double count if its for week 2 only? "Joel" wrote: You will need to convert the date to a week number. There are plenty of request at this website for conversion from a date to a week number (do a search). I can help with the conversion but not sure how your week numbers work. do they start on the first sunday of the month or start on saturday. Week numbers vary from company to company. The function WeekNum() may work. "Owl" wrote: Hi Joel, Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
Hi Joel,
I think the layout of my data is causing problems with making this work. This is my data Revised Week Description VPN SumOfSales ex vat 3 e 9781405239318 21.89 5 f 9781405238243 23.88 5 g 9781405238243 0.01 2 h 9781405238243 3.99 2 i 9781405238243 27.86 Below is my sheet where i want it to feed into. Start Week End Week ISBN Sales data 5 5 9781405239271 5 10 9781405239318 2 5 9781405238243 27.88 5 10 9781405238236 - So, in the sales data, i used the sumproduct formula you recommended which only picked up the data for the particular ISBN code. But id also need to introduce the start week/end week variable. Eg : the sales data column should pull through the sum of all data for that ISBN code for weeks x to y. So, just to be crude in describing, i just think it just needs to be almost a double sumif, ie sum if week = 10 AND code = whatever code specified LESS sumif week = 5 AND code = whatever code specified. "Joel" wrote: I would still do it in 2 steps. First add each column using the following formula Assume this formula is in cell B30. Then copy in row 30 from columns B to BZ. =sumProduct(--($A3:$A25=9781405230278),B3:B25) Then the total for week 2 - 3 would be (row one would be where your work weeks are located) =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B30:BZ30) "Owl" wrote: Hi That didnt work so ive decided to go back to the original data all in columns and use that. Prior to this, it was all in pivots and so confusing. It has sales data by week and by code all in columns. What i now need to do is put in a formula to capture all sales for a specific code and specific weeks - im thinking a nested sumif? unless there is another way of specifying summing only the rows which have certain weeks and certain products? "Joel" wrote: Use sumproduct for sums at bottom of each column =sumProduct(--($A3:$A25=9781405230278),B3:B25) Then copy the formula acroos all columns. Notice I put a dollar sign in front of the references to column A so they don't change when you copy. "Owl" wrote: Thanks Joel - youve been a great help! Just needed one more small thing - ive amended the =2 to =cell reference in my schedule. Is there a way of only summing certain rows eg : for weeks 2-5, i want to pull through data for a specific product (which is in rows on my data schedule) Eg : Week 2 Week 3 Sales between Weeks 3 and 2 Product number *sumproduct formulae 9781405230278 And id want data from the below table Week Numbers ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 Would it be a combination of vlookup/sumifs? "Joel" wrote: I would sum each column (my example in row 25) and use sumproduct. If you have 52 weeks which would be columns B to BZ. Then the following formula can be used =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B25:BZ25) "Owl" wrote: Hi Joel, I finally converted the weeks to week number and am now trying to link this to my other file Week Number ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 So, for example, how would i sum up weeks 2 and 3 only? Would it be sumif on week 3 less sumif week 1. And if i used the < formula, how would i ensure i dont double count if its for week 2 only? "Joel" wrote: You will need to convert the date to a week number. There are plenty of request at this website for conversion from a date to a week number (do a search). I can help with the conversion but not sure how your week numbers work. do they start on the first sunday of the month or start on saturday. Week numbers vary from company to company. The function WeekNum() may work. "Owl" wrote: Hi Joel, Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
Another method if you ID value is only one row or continuous row is to use
Offset and wum the area defined by the offset function like below =Sum(OFFSET(reference,rows,cols,height,width))Anot her method if you ID value Reference = the start of your table like Sheet1!A1 Rows = is the row number that you can get using Match function Match(9781405239271,Sheet1!A1:A25,0) Cols = is the column Number you can get which is the first Week using Match Match(2,Sheet1!AB1:BZ1,0) Height = 1 Row. Or multiple rows if the ID number is on more than one row Width = the number of weeks which is the End Week N umber minus the Start Week Number + 1 (3 - 2 + 1) for week 2 to week 3. =Sum(OFFSET(Sheet1!A1,Match(9781405239271,Sheet1!A 1:A25,0),Match(2,Sheet1!AB1:BZ1,0),1,2)) "Owl" wrote: Hi Joel, I think the layout of my data is causing problems with making this work. This is my data Revised Week Description VPN SumOfSales ex vat 3 e 9781405239318 21.89 5 f 9781405238243 23.88 5 g 9781405238243 0.01 2 h 9781405238243 3.99 2 i 9781405238243 27.86 Below is my sheet where i want it to feed into. Start Week End Week ISBN Sales data 5 5 9781405239271 5 10 9781405239318 2 5 9781405238243 27.88 5 10 9781405238236 - So, in the sales data, i used the sumproduct formula you recommended which only picked up the data for the particular ISBN code. But id also need to introduce the start week/end week variable. Eg : the sales data column should pull through the sum of all data for that ISBN code for weeks x to y. So, just to be crude in describing, i just think it just needs to be almost a double sumif, ie sum if week = 10 AND code = whatever code specified LESS sumif week = 5 AND code = whatever code specified. "Joel" wrote: I would still do it in 2 steps. First add each column using the following formula Assume this formula is in cell B30. Then copy in row 30 from columns B to BZ. =sumProduct(--($A3:$A25=9781405230278),B3:B25) Then the total for week 2 - 3 would be (row one would be where your work weeks are located) =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B30:BZ30) "Owl" wrote: Hi That didnt work so ive decided to go back to the original data all in columns and use that. Prior to this, it was all in pivots and so confusing. It has sales data by week and by code all in columns. What i now need to do is put in a formula to capture all sales for a specific code and specific weeks - im thinking a nested sumif? unless there is another way of specifying summing only the rows which have certain weeks and certain products? "Joel" wrote: Use sumproduct for sums at bottom of each column =sumProduct(--($A3:$A25=9781405230278),B3:B25) Then copy the formula acroos all columns. Notice I put a dollar sign in front of the references to column A so they don't change when you copy. "Owl" wrote: Thanks Joel - youve been a great help! Just needed one more small thing - ive amended the =2 to =cell reference in my schedule. Is there a way of only summing certain rows eg : for weeks 2-5, i want to pull through data for a specific product (which is in rows on my data schedule) Eg : Week 2 Week 3 Sales between Weeks 3 and 2 Product number *sumproduct formulae 9781405230278 And id want data from the below table Week Numbers ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 Would it be a combination of vlookup/sumifs? "Joel" wrote: I would sum each column (my example in row 25) and use sumproduct. If you have 52 weeks which would be columns B to BZ. Then the following formula can be used =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B25:BZ25) "Owl" wrote: Hi Joel, I finally converted the weeks to week number and am now trying to link this to my other file Week Number ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 So, for example, how would i sum up weeks 2 and 3 only? Would it be sumif on week 3 less sumif week 1. And if i used the < formula, how would i ensure i dont double count if its for week 2 only? "Joel" wrote: You will need to convert the date to a week number. There are plenty of request at this website for conversion from a date to a week number (do a search). I can help with the conversion but not sure how your week numbers work. do they start on the first sunday of the month or start on saturday. Week numbers vary from company to company. The function WeekNum() may work. "Owl" wrote: Hi Joel, Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - sumif help on sales data between dates?
Thanks Joel, - youre a star! Worked perfectly - only small thing was the
match function didnt work for some of my product codes because i think some have apostrophes and some dont - i assume they mean its text, and the only way it works is if i try and add apostrophes to them all! "Joel" wrote: Another method if you ID value is only one row or continuous row is to use Offset and wum the area defined by the offset function like below =Sum(OFFSET(reference,rows,cols,height,width))Anot her method if you ID value Reference = the start of your table like Sheet1!A1 Rows = is the row number that you can get using Match function Match(9781405239271,Sheet1!A1:A25,0) Cols = is the column Number you can get which is the first Week using Match Match(2,Sheet1!AB1:BZ1,0) Height = 1 Row. Or multiple rows if the ID number is on more than one row Width = the number of weeks which is the End Week N umber minus the Start Week Number + 1 (3 - 2 + 1) for week 2 to week 3. =Sum(OFFSET(Sheet1!A1,Match(9781405239271,Sheet1!A 1:A25,0),Match(2,Sheet1!AB1:BZ1,0),1,2)) "Owl" wrote: Hi Joel, I think the layout of my data is causing problems with making this work. This is my data Revised Week Description VPN SumOfSales ex vat 3 e 9781405239318 21.89 5 f 9781405238243 23.88 5 g 9781405238243 0.01 2 h 9781405238243 3.99 2 i 9781405238243 27.86 Below is my sheet where i want it to feed into. Start Week End Week ISBN Sales data 5 5 9781405239271 5 10 9781405239318 2 5 9781405238243 27.88 5 10 9781405238236 - So, in the sales data, i used the sumproduct formula you recommended which only picked up the data for the particular ISBN code. But id also need to introduce the start week/end week variable. Eg : the sales data column should pull through the sum of all data for that ISBN code for weeks x to y. So, just to be crude in describing, i just think it just needs to be almost a double sumif, ie sum if week = 10 AND code = whatever code specified LESS sumif week = 5 AND code = whatever code specified. "Joel" wrote: I would still do it in 2 steps. First add each column using the following formula Assume this formula is in cell B30. Then copy in row 30 from columns B to BZ. =sumProduct(--($A3:$A25=9781405230278),B3:B25) Then the total for week 2 - 3 would be (row one would be where your work weeks are located) =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B30:BZ30) "Owl" wrote: Hi That didnt work so ive decided to go back to the original data all in columns and use that. Prior to this, it was all in pivots and so confusing. It has sales data by week and by code all in columns. What i now need to do is put in a formula to capture all sales for a specific code and specific weeks - im thinking a nested sumif? unless there is another way of specifying summing only the rows which have certain weeks and certain products? "Joel" wrote: Use sumproduct for sums at bottom of each column =sumProduct(--($A3:$A25=9781405230278),B3:B25) Then copy the formula acroos all columns. Notice I put a dollar sign in front of the references to column A so they don't change when you copy. "Owl" wrote: Thanks Joel - youve been a great help! Just needed one more small thing - ive amended the =2 to =cell reference in my schedule. Is there a way of only summing certain rows eg : for weeks 2-5, i want to pull through data for a specific product (which is in rows on my data schedule) Eg : Week 2 Week 3 Sales between Weeks 3 and 2 Product number *sumproduct formulae 9781405230278 And id want data from the below table Week Numbers ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 Would it be a combination of vlookup/sumifs? "Joel" wrote: I would sum each column (my example in row 25) and use sumproduct. If you have 52 weeks which would be columns B to BZ. Then the following formula can be used =SUMPRODUCT(--(B1:BZ1=2),--(B1:BZ1<=3),B25:BZ25) "Owl" wrote: Hi Joel, I finally converted the weeks to week number and am now trying to link this to my other file Week Number ID Code 1 2 3 4 9781405230278 826.62 682.86 634.94 1405217367 299.25 379.05 359.10 So, for example, how would i sum up weeks 2 and 3 only? Would it be sumif on week 3 less sumif week 1. And if i used the < formula, how would i ensure i dont double count if its for week 2 only? "Joel" wrote: You will need to convert the date to a week number. There are plenty of request at this website for conversion from a date to a week number (do a search). I can help with the conversion but not sure how your week numbers work. do they start on the first sunday of the month or start on saturday. Week numbers vary from company to company. The function WeekNum() may work. "Owl" wrote: Hi Joel, Thanks for this - are you saying apply this formula to 10/8/08 and it will summarise it somehow? My weeks look like this 10/01/2008 10/01/2008 Sales look like this - via a pivot - and the weeks go 1,2,3,4 then 1,2,3,4 rather than 1-52 Month Week Number 01-08 Description 1 2 3 x 937.6 908.67 968.04 "Joel" wrote: Look for week numbers greate than X and less then Y. Don't use dates. The formula below will get the week numbers out of your data by using the MID function =IF(AND(VALUE(MID(A1,6,2))=1,VALUE(MID(A1,6,2))<= 2),TRUE,FALSE) "Owl" wrote: Hi, I want to somehow pull through sales data for between two dates. I have the dates as 3/1/08 and the sales data has come through by week. How could i pull through the info for between two dates? EG : sales data looks like this Week 1 3000 Week 2 5000 But the dates i need are between 1.1.08 and 18.2.08 for specific products. I was planning on doing lookups to get the product, somehow linking to dates (Concatenate) but then i was lost in how to get sales BETWEEN two dates.... Any thoughts out there? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sales Order to Sales History Data | Excel Worksheet Functions | |||
Linking Sales Order to Sales History Data | Excel Worksheet Functions | |||
Sumif on pulling out data from dates | Excel Worksheet Functions | |||
sales dates | Excel Discussion (Misc queries) | |||
Sum of data between two dates - tried SUMIF and it returned "0" | Excel Worksheet Functions |