SUMPRODUCT 5 criteria

1. 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

2. Re: SUMPRODUCT 5 criteria

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.

3. Re: SUMPRODUCT 5 criteria

That certainly looks cleaner. I don't have access to the sheet until I get to to work but will post back once I can try it out. Thanks for the guidance.

4. Re: SUMPRODUCT 5 criteria

Worked perfectly, as I'm sure you knew it would.

Many thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1