Sumproduct with more than 2 criteria

[poolf01]

Hi,

I have been looking at the message board and have come across some really useful tips. Great Stuff! I've managed to adapt a formula to search for multiple criteria, but the formula is not bring back the correct results. Formula looks like this :

=SUMPRODUCT((A3:A65536="1")*(E3:E65536="ISL")*(D3:D65536="ANOTHER")*(B3:B65536="2006")*(F3:F65536))

so,
If Column A contains 1 and Column E contains ISL and Column D contains ANOTHER and Column B contains 2006, bring back total number of hours (i.e. sum column F). The correct answer should be 63, but this formula brings back 0

Any ideas? Is it that you can't select so much search criteria in this particular formula structure?

Thanks

 

[Richard Schollar]

Hi

If the numbers in column A and B are genuine numerics rather than text, then you don't need the "" around them ie

=SUMPRODUCT((A3:A65536=1)*(E3:E65536="ISL")*(D3:D65536="ANOTHER")*(B3:B65536=2006)*(F3:F65536))