SUMIF with multiple variables HELP!!!!

[Gwynne]

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*(DataFile.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 doesn’t 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)*(DataFile.xls’!Project=$J$1)*(DataFile.xls’!Project=$L$1)*(DataFile.xls’!Date>=$M$1)*(DataFile.xls’!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]

Consider using the SUMPRODUCT function instead.

 

[Gwynne]

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((...?