Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separating invoices by invoice date then summing by quarter
Hi folks...
On the 1st sheet of my document I have a proposal/invoice tracker in which the last column for each row is N and contains the date that I create the invoice, data that I enter manually. On sheet 2 I have set up a calculator that does all the math for me to produce my final tax payment. I have copied the table 4 times and formatted by color so that each represents one quarter, 3 months. There are only 2 pieces of information that I need to enter in order to get the final calculation: 1) The amount I invoiced - see below 2) my expenses - this is something I track elsewhere and input manually so I don't need help on this one. My problem is how to create a conditional statement to grab the amounts from sheet one and place them into the appropriate cell in sheet 2. The logic I need to create is as follows... For cell B5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Jan 1 - Mar 31 For cell E5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Apr 1 - Jun 30 For cell H5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Jul 1 - Sep 30 For cell K5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Oct 1 - Dec 31 How can I go about doing this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separating invoices by invoice date then summing by quarter
Assuming ONE calendar year, then
for Jan-Mar: (B5) =SUMPRODUCT(--(Sheet1!N1:N1000<""),--(MONTH(Sheet1!N1:N1000)=1),--(MONTH(Sheet1!N1:N1000)<=3),Sheet1!E1:E1000) for Apr-Jun: (E5) =SUMPRODUCT(--(Sheet1!N1:N1000<""),--(MONTH(Sheet1!N1:N1000)=4),--(MONTH(Sheet1!N1:N1000)<=6),Sheet1!E1:E1000) repeat for other quarters For SUMPRODUCT, ranges cannot be a whole column: changes ranges to suit your need. HTH "neroamdrid" wrote: Hi folks... On the 1st sheet of my document I have a proposal/invoice tracker in which the last column for each row is N and contains the date that I create the invoice, data that I enter manually. On sheet 2 I have set up a calculator that does all the math for me to produce my final tax payment. I have copied the table 4 times and formatted by color so that each represents one quarter, 3 months. There are only 2 pieces of information that I need to enter in order to get the final calculation: 1) The amount I invoiced - see below 2) my expenses - this is something I track elsewhere and input manually so I don't need help on this one. My problem is how to create a conditional statement to grab the amounts from sheet one and place them into the appropriate cell in sheet 2. The logic I need to create is as follows... For cell B5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Jan 1 - Mar 31 For cell E5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Apr 1 - Jun 30 For cell H5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Jul 1 - Sep 30 For cell K5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Oct 1 - Dec 31 How can I go about doing this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separating invoices by invoice date then summing by quarter
Hi
One way Cell B5 =SUMPRODUCT((Sheet1!$N$2:$N$1000,<=Date(2006,3,31) )* (Sheet1!$E$2:$E$1000)) Cell E5 =SUMPRODUCT((Sheet1!$N$2:$N$1000,Date(2006,3,31)) * (Sheet1!$N$2:$N$1000,<=Date(2006,6,30))* (Sheet1!$E$2:$E$1000)) Repeat above for cells H5 and K5 and adjust Dates accordingly Change ranges to suit, but note that you cannot use whole column ranges with Sumproduct, and, ranges must be of equal size. If you were able to insert a new row 1 on your Sheet 2 and put the quarter end date in cells B1, E1 etc. then you could amend the second formula to =SUMPRODUCT((Sheet1!$N$2:$N$1000,B$1)* (Sheet1!$N$2:$N$1000,<=E$1)* (Sheet1!$E$2:$E$1000)) and just copy to H5 and K5 without amendment. Also note, if your sheet names have spaces, e.g. Sheet 1, then you need to wrap the sheet names in single quotes e.g. 'Sheet 1'!$N$2:$N$1000 -- Regards Roger Govier "neroamdrid" wrote in message ... Hi folks... On the 1st sheet of my document I have a proposal/invoice tracker in which the last column for each row is N and contains the date that I create the invoice, data that I enter manually. On sheet 2 I have set up a calculator that does all the math for me to produce my final tax payment. I have copied the table 4 times and formatted by color so that each represents one quarter, 3 months. There are only 2 pieces of information that I need to enter in order to get the final calculation: 1) The amount I invoiced - see below 2) my expenses - this is something I track elsewhere and input manually so I don't need help on this one. My problem is how to create a conditional statement to grab the amounts from sheet one and place them into the appropriate cell in sheet 2. The logic I need to create is as follows... For cell B5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Jan 1 - Mar 31 For cell E5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Apr 1 - Jun 30 For cell H5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Jul 1 - Sep 30 For cell K5 on sheet 2 (Q1) I want to include all values from Column E on sheet 1 where the date in Column N of that row falls between Oct 1 - Dec 31 How can I go about doing this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating date from a Date & Time stamp | Excel Discussion (Misc queries) | |||
Can I group overdue Invoices by date from an Excel worksheet? | Excel Worksheet Functions | |||
Auto format quarter start date | Excel Discussion (Misc queries) | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
How do I set date to change automatically on invoices created wit. | New Users to Excel |