Sum product based on Multiple Criteria

[Barieq]

Hi,

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

Start Date
End Date
Acct. No
Font Colour

[Richard Buttrey]

Hi,

Try this function

Please Login or Register  to view this content.

Name
B41 'dtStart'
B42 'dtEnd
G42 'fontcolour

[kvsrinivasamurthy]

Try this in C43 thendragged across

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

[Barieq]

hi Richard,

How can get the result using you Function

[kvsrinivasamurthy]

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)

[Glenn Kennedy]

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.

[Richard Buttrey]

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

Please Login or Register  to view this content.

See workbook attached

[Barieq]

Hi Glenn,

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

[Barieq]

Thank you Richard & Glenn, excellent. Solved..