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, |
SUMIF with multiple variables HELP!!!!
Consider using the SUMPRODUCT function instead.
-- Gary''s Student - gsnu200860 "Gwynne" wrote: 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, |
SUMIF with multiple variables HELP!!!!
Gary's Student - thank's so much for your quick response. However, when I
looked up how to use SUBPRODUCT, I could not figure out how to apply it to my formula. Can U help with this too? Do I just replace the =SUM(IF(( with =SUBPRODUCT(IF((...? "Gary''s Student" wrote: Consider using the SUMPRODUCT function instead. -- Gary''s Student - gsnu200860 "Gwynne" wrote: 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, |
All times are GMT +1. The time now is 11:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com