Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with multiple variables HELP!!!!
I have an Array formula below that works perfect. It sums amounts of project
expenses (DataFile.xls) based on specified criteria from multiple combo boxes. This part works perfectly. {=SUM(IF((DataFile.xls!Account=$A15)*(DataFile. xls!Dept=C$6)*(DataFile.xls!Project=$H$1*(Da taFile.xls!Date=$M$1)*(DataFile.xls!Date<=$ N$1),DataFile.xls!Amount))} However, there are several incorrect project Codes that could also be used in this expense file (DataFile.xls) from Fiscal Yeas 08, 07 and 06. So, from the combo box, when a project is selected, I also with vlookup pull the other possible Fiscal year project names. That way I was hopping we could sum all the expenses for that project even if it were posted to the incorrect Fiscal Year project name. The lookup data table is below: My problem is that if there is no data in the Data file that fits all 3 possible project names, it doesnt pull up anything. Sometimes there can be 3 project names, sometimes 2, sometimes only 1. How do I write this formula so that it adds amounts for all three if there are three; only 2 if there are only two; and just 1 if there is just 1? Below is the formula to include all 3 fiscal year project names IF THEY APPEAR in the expense file {=SUM(IF((DataFile.xls!Account=$A15)*(DataFile. xls!Dept=C$6)*(DataFile.xls!Project=$H$1)*(D ataFile.xls!Project=$J$1)*(DataFile.xls!Proj ect=$L$1)*(DataFile.xls!Date=$M$1)*(DataFile.x ls!Date<=$N$1),DataFile.xls!Amount))} Oh, I do hope I have explained this properly and SOMEONE can help me out there! Thanks soo much, |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif with 2 variables | Excel Discussion (Misc queries) | |||
SUMIF with two or more variables | Excel Worksheet Functions | |||
Sumif with multiple variables | Excel Discussion (Misc queries) | |||
SUMIF Formuale with 2 variables | Excel Worksheet Functions | |||
SUMIF variables | Excel Discussion (Misc queries) |