Sumproduct using indirect and with some text cells in array

[Manwithaplan]

Hi all,

Following on from my last question (re - 'Using sumproduct when array contains some cells with text'. I would like to use the following sumproduct and indirect formula to sum values in an array which also contains some cells with text. I could not apply the iferror fix to this one but maybe I was messed up the placement of the brackets. Any help will be appreciated.

Formula currently being used = SUMPRODUCT(INDIRECT("'"&D$4&"'!E10:G300"),((INDIRECT("'"&D$4&"'!E9:G9"))=Reconcile!$C5))*((INDIRECT("'"&D$4&"'!A10:A300")=Reconcile!$B5))

Answer should= 100 - in cell D5 of 'Reconcile' tab

See uploaded workbook for full example

Thanks

Mike

[protonLeah]

The first two array sizes are mismatched:
E10:G300 v.s. E9:G9

is one cause of an error: The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

[Manwithaplan]

E9:G9 is the horizontal criteria array (so sits above the data array). How else would you do this?

[protonLeah]

The layout of your sheets makes it difficult. Try this one for starters:

Please Login or Register  to view this content.

[Manwithaplan]

Hi, its weird it seems to only work for Purchase and Sale criteria, for some reason 'Internal' criteria is coming back #N/A, any idea why?

[protonLeah]

My mistake (match type) it should be:

Please Login or Register  to view this content.