Help - sumif help on sales data between dates?

[Owl]

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?

 

[Joel]

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]

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]

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]

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

 

[Joel]

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]

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
Would it be a combination of vlookup/sumifs?

 

[Joel]

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]

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]

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]

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]

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))Another 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!A1:A25,0),Match(2,Sheet1!AB1:BZ1,0),1,2))

 

[Owl]

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!