Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup query
Hi, thanks for reading this. I have a 2 page workbook with invoice data
entry on the first page and i am looking to pull off totals onto the 2nd page based on month number in the date column. My date column (B) contains formatted date dd/mm/yy and for example column F contains the invoice sub total in currency. My invoice dates are not in date order. I want to have a row of fields on Sheet2 which have monthly totals in summed from the data in sheet1, for example sum all the invoice totals of the whole sheet if those invoices have the date month of 04. I think I want to use VLOOKUP, maybe with Right or MID to pick the month out of the date cell. What i am not sure about is how to put the function into a range and is it all wrapped in a SUMIF? Any help much appreciated, thank you. Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup query
Vlookup is meant to bring back a single value, where SUMIF can add multiple
cells based on a single criterion or, as in your case, a continuous range of a single variable such as the date. I'd try =sumif(Sheet1!b:b,"=" & date(2005,4,1),Sheet1!d:d) - sumif(Sheet1!b:b,"=" & date(2005,5,1),Sheet1!d:d) to get the total of invoices from April (all those on or after 4/1 MINUS all those on or after 5/1 leave only those from April). Instead of the 4 and 5 shown in the dates, you could just as well use cell references, say A5 and A5+1. HTH. --Bruce "Tester" wrote: Hi, thanks for reading this. I have a 2 page workbook with invoice data entry on the first page and i am looking to pull off totals onto the 2nd page based on month number in the date column. My date column (B) contains formatted date dd/mm/yy and for example column F contains the invoice sub total in currency. My invoice dates are not in date order. I want to have a row of fields on Sheet2 which have monthly totals in summed from the data in sheet1, for example sum all the invoice totals of the whole sheet if those invoices have the date month of 04. I think I want to use VLOOKUP, maybe with Right or MID to pick the month out of the date cell. What i am not sure about is how to put the function into a range and is it all wrapped in a SUMIF? Any help much appreciated, thank you. Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup query
Thanks Bruce - I had worked out that SUMIF would help but was still trying
to incorporate it into VLOOKUP as an array, duh!!!! Chris "bpeltzer" wrote in message ... Vlookup is meant to bring back a single value, where SUMIF can add multiple cells based on a single criterion or, as in your case, a continuous range of a single variable such as the date. I'd try =sumif(Sheet1!b:b,"=" & date(2005,4,1),Sheet1!d:d) - sumif(Sheet1!b:b,"=" & date(2005,5,1),Sheet1!d:d) to get the total of invoices from April (all those on or after 4/1 MINUS all those on or after 5/1 leave only those from April). Instead of the 4 and 5 shown in the dates, you could just as well use cell references, say A5 and A5+1. HTH. --Bruce "Tester" wrote: Hi, thanks for reading this. I have a 2 page workbook with invoice data entry on the first page and i am looking to pull off totals onto the 2nd page based on month number in the date column. My date column (B) contains formatted date dd/mm/yy and for example column F contains the invoice sub total in currency. My invoice dates are not in date order. I want to have a row of fields on Sheet2 which have monthly totals in summed from the data in sheet1, for example sum all the invoice totals of the whole sheet if those invoices have the date month of 04. I think I want to use VLOOKUP, maybe with Right or MID to pick the month out of the date cell. What i am not sure about is how to put the function into a range and is it all wrapped in a SUMIF? Any help much appreciated, thank you. Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AHHH! Again | Excel Discussion (Misc queries) | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
Vlookup or If query | Excel Discussion (Misc queries) | |||
Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions | |||
Vlookup query | Excel Worksheet Functions |