Help with Sumproduct formula to disregard a column if not applicable.

[tml2424]

I need assistance with a sumproduct formula. I'd like it to look up 3 criteria (Job, State and Location, if applicable) and pull in the rate. For the most part the formula works but if I change the dropdown to "Location 2", it doesn't work since the cells in Column L are blank. Basically, I'm trying to pull in rates for different locations that have a unique rate (ie: name 1) but for all other locations in Arizona (Location 2) it would disregard column L and just use the standard rate in column N. Column L throws me off since I want to say that if the name is not listed in column L and it's blank, then just use the standard rate. Not sure if I'm explaining this well but perhaps the spreadsheet will help which I've attached.

=SUMPRODUCT((Calculator!$K$5:$K$10=$A11)*(Calculator!$L$5:$L$10=$A$5)*(Calculator!$M$5:$M$10=$A$7)*(D$10>=$B11)*Calculator!$N$5:$N$10)

[protonLeah]

I don't see any role for the dates in the lookup table

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts braces {} around the formula (though do not attempt to manually insert these yourself).


Why the .xls file vs. xlsx?

[bebo021999]

Move (C$10>=$B11) as condition out of SUMPRODUCT
A5 should be replaced by: IF(COUNTIF($L$5:$L$17,$A$5),$A$5,"") says: If A5 exist, A5, else, "" (blank)
Finally,

Please Login or Register  to view this content.

[tml2424]

Thanks ProtonLeah. For some reason I tried uploading a .xlsx, .xlxb, .xlsm and none worked. The only file type that worked was .xls. Not sure why that was.

The formula you provided only appears to work for "Location 1" but if I change the drop down to any other Location, it is giving me a #n/a.

[tml2424]

Thanks bebo. This appears to be super close and works for every location except "Location 7" in the drop down. I believe this has to do with the fact that Location 7 is in column L but only for 1 job. Hmmmm any thoughts?

[protonLeah]

Try (with date comparison added) added IFERROR() for the case of no location specified.:

Please Login or Register  to view this content.

[tml2424]

ProtonLeah....that formula did the trick. Thank you!

bebo021999 - I have no idea why your formula didn't work (Location 7) but conceptually it makes sense and seems like it should work. That one puzzles me.