SUMPRODUCT 5 criteria

I have a summary table based on a multiple criteria SUMPRODUCT formula.

=IF(ISNUMBER(C6),SUMPRODUCT((AMOUNT>=(B6))*(AMOUNT<=(C6))*(CITY=($E$5))),"0")

The names of the Named ranges (single columns in master sheet) have been renamed to something rather more descriptive than appears in the sheet itself. (DAYS_OLD is also a named range of a column on the master sheet to clarify what follows)

I have been asked whether it is possible to add an extra layer of counting based on "Days old" column in the master sheet or more precisely between x days old and y days. For example if the amount band was between £1000 to £10000 and the office involved is our London branch then..

Amount low £1000 Amount High£10000, City=London, "Days old" between 10 & 30
Amount low £1000 Amount High£10000, City=London, "Days old" =between 30 & 50 and so on...

The cell references have been removed in favour of the actual amounts to try and make it more transparent.

I thought if I followed the format of the existing SUMPRODUCT part it might work and ended up with this:

=IF(ISNUMBER(C6),SUMPRODUCT((AMOUNT>=1000)*(AMOUNT<=10000)*(CITY=($E$5)*SUMPRODUCT(DAYS_OLD>=10)*SUMPRODUCT(DAYS_OLD<=30)),"0")

I get a value error. I suspect either my asterisk or brackets are in the wrong order?

All suggestions welcome

Try this

Formula:

Please Login or Register  to view this content.

You had some weird parentheses thrown in and was invoking the SUMPRODUCT() function when you didn't need to.