Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF/AND combination?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF/AND combination?
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF/AND combination?
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUBTOTAL and COUNTIF (or SUMIF) combination | Excel Worksheet Functions | |||
Countif and sumif combination problem | Excel Worksheet Functions | |||
Combination Sum | Excel Worksheet Functions | |||
Combination | Charts and Charting in Excel | |||
SUMIF - HLOOKUP Combination | Excel Worksheet Functions |