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,

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,

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,