# Sum product based on Multiple Criteria

1. ## Sum product based on Multiple Criteria

Hi,

I need help in solving the SumProduct formula based on multiple criteria

Start Date
End Date
Acct. No
Font Colour

2. ## Re: Sum product based on Multiple Criteria

Hi,

Try this function

``Please Login or Register  to view this content.``
Name
B41 'dtStart'
B42 'dtEnd
G42 'fontcolour

3. ## Re: Sum product based on Multiple Criteria

Try this in C43 thendragged across

=SUMPRODUCT(((A2:A37<=B41)+(A2:A37>=B42))*(E2:E37=B43)*F2:F37)

4. ## Re: Sum product based on Multiple Criteria

hi Richard,

How can get the result using you Function

5. ## Re: Sum product based on Multiple Criteria

Try this in C43 thendragged across

=SUMPRODUCT(((\$A\$2:\$A\$37<=\$B\$41)+(\$A\$2:\$A\$37>=\$B\$42))*(\$E\$2:\$E\$37=\$B43)*F2:F37)

6. ## Re: Sum product based on Multiple Criteria

I think this is what you need:

=SUMPRODUCT(--(\$A\$2:\$A\$37>=\$B\$41)*(\$A\$2:\$A\$37<=\$B\$42)*(\$E\$2:\$E\$37=\$B43)*TestFont(\$F\$2:\$F\$37,\$G\$42)*F\$2:F\$37)

in C43, copied across and down.

7. ## Re: Sum product based on Multiple Criteria

Put the Function in a VBA Module.

Alt-F11 to go to VBA, find your workbook in the VBA Project window on the left, choose one of the objects - a sheet or the workbook object and from the menu choose Insert Module and copy the code I gave.

Then in the worksheet treat that function as a normal Excel function and enter
Formula:
`Please Login or Register  to view this content.`

See workbook attached

8. ## Re: Sum product based on Multiple Criteria

Hi Glenn,

Thanks, at present I am out of office. I will check and get back.

9. ## Re: Sum product based on Multiple Criteria

Thank you Richard & Glenn, excellent. Solved..

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