Prev: Highest number + company
Next: Pivot Table Filter Formula
From: Michael on 19 Mar 2010 06:43
I have a spreadsheet with data that shows transactions for a number of areas
with a column showing which week the transaction belongs to eg (I have put
row and column headings on for clarity later):

Sheet1

A B C D
1 Area Week No Value Description
2 1 10 52.25 bread
3 1 10 14.12 milk
4 1 11 15.25 bread
5 2 12 28.24 butter

etc

I then want to populate another sheet with these transactions summarised by
area

Sheet2

A B C D
1 Area Week 10 Week 11 Week 12
2 1 66.37 15.25 0
3 2 0 0 28.24

I can't use a pivot table because I have a number of different worksheets
for different suppliers that are in a different format that also need to be
shown on the summary sheet.

I'm thinking that this could be done by using a sumif formula on sheet 2
that refers to both the area and week number. Is this possible please?




From: Bernard Liengme on 19 Mar 2010 07:48
I entered you transaction data on a worksheet named "My Data" with the same
layout as yours.
My Summary sheet looks like you Sheet, except in B1,C1,D1... I have 10,
11,12.... as I need real number in my formula
I can make these display as Week 10, Week 11, etc by using a custom format
of: "Week " #
In B2 of Summary sheet I have
=SUMPRODUCT(--('My Data'!$A$2:$A$5=$A2),--('My Data'!$B$2:$B$5=B$1),'My
Data'!$C$2:$C$5)
This displays 66.37 as needed
Of course, in reality you will need to adjust the ranges - I worked with
just the 4 rows in the My Data sheet. You will need something like
=SUMPRODUCT(--('My Data'!$A$2:$A$500=$A2),--('My Data'!$B$2:$B$500=B$1),'My
Data'!$C$2:$C$500)
Be careful with the $ to make things absolute and mixed references
The formula is copied down and across as needed
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Michael" <Michael(a)discussions.microsoft.com> wrote in message
news:D36E4CF0-734D-4195-A235-83FD74B95021(a)microsoft.com...
> I have a spreadsheet with data that shows transactions for a number of
> areas
> with a column showing which week the transaction belongs to eg (I have put
> row and column headings on for clarity later):
>
> Sheet1
>
> A B C D
> 1 Area Week No Value Description
> 2 1 10 52.25 bread
> 3 1 10 14.12 milk
> 4 1 11 15.25 bread
> 5 2 12 28.24 butter
>
> etc
>
> I then want to populate another sheet with these transactions summarised
> by
> area
>
> Sheet2
>
> A B C D
> 1 Area Week 10 Week 11 Week 12
> 2 1 66.37 15.25 0
> 3 2 0 0 28.24
>
> I can't use a pivot table because I have a number of different worksheets
> for different suppliers that are in a different format that also need to
> be
> shown on the summary sheet.
>
> I'm thinking that this could be done by using a sumif formula on sheet 2
> that refers to both the area and week number. Is this possible please?
>
>
>
>
From: Eduardo on 19 Mar 2010 07:54
Hi,
in sheet 2 the columns heather left as 10 for week 10, 11 for week 11, ....
you can add a row at the top and center a title like " Week number" if you
want if you add this row the formula will go in cell B3 otherwise in cell B2

=sumproduct(--($a$2=sheet1!$A$2:$A$1000),--(B$1=sheet1!$B$2:$B$1000),sheet1!$C$2:$C$1000)

"Michael" wrote:

> I have a spreadsheet with data that shows transactions for a number of areas
> with a column showing which week the transaction belongs to eg (I have put
> row and column headings on for clarity later):
>
> Sheet1
>
> A B C D
> 1 Area Week No Value Description
> 2 1 10 52.25 bread
> 3 1 10 14.12 milk
> 4 1 11 15.25 bread
> 5 2 12 28.24 butter
>
> etc
>
> I then want to populate another sheet with these transactions summarised by
> area
>
> Sheet2
>
> A B C D
> 1 Area Week 10 Week 11 Week 12
> 2 1 66.37 15.25 0
> 3 2 0 0 28.24
>
> I can't use a pivot table because I have a number of different worksheets
> for different suppliers that are in a different format that also need to be
> shown on the summary sheet.
>
> I'm thinking that this could be done by using a sumif formula on sheet 2
> that refers to both the area and week number. Is this possible please?
>
>
>
>
 | 
Pages: 1
Prev: Highest number + company
Next: Pivot Table Filter Formula