Convert formula from =SUMIF to =SUMPRODUCT

[Aaronawl]

Hi,

I am trying to convert all my SUMIF formulas to SUMPRODUCT so that the referenced workbook does not need to be open.
I have tried myself but cannot seem to get it to work. Here is an example of the formula:

=SUMIFS('[Mileage Log 2018.xlsm]Advisors'!$D$4:$D$15,'[Mileage Log 2018.xlsm]Advisors'!$E$4:$E$15,$B$9,'[Mileage Log 2018.xlsm]Advisors'!$E$4:$E$15,$B$9)


B9 refers to a date [DD/MM/YY] which decides which day to sum the figures from.


I have tried the following adjustment but only seem ot get a #VALUE error:

=SUMPRODUCT(--('[Mileage Log 2018.xlsm]Advisors'!$D$4:$D$15),--('[Mileage Log 2018.xlsm]Advisors'!$E$4:$E$15,$B$9,'[Mileage Log 2018.xlsm]Advisors'!$E$4:$E$15,$B$9)


Many thanks in advance,

 

[Aladin Akyurek]

Like this:

=SUMPRODUCT('[Mileage Log 2018.xlsm]Advisors'!$D$4:$D$15,--('[Mileage Log 2018.xlsm]Advisors'!$E$4:$E$15=$B$9))

 

[Aaronawl]

Like this:

=SUMPRODUCT('[Mileage Log 2018.xlsm]Advisors'!$D$4:$D$15,--('[Mileage Log 2018.xlsm]Advisors'!$E$4:$E$15=$B$9))

That worked perfect! thank you. I've managed to adjust the similar formulas using your help.

There is another one i'm struggling with as it has 2 parameters that it searches for. Could you help me with:

=SUMIFS('[Mileage Log 2018.xlsm]DB, AH & KA'!$O$3:$O$200,'[Mileage Log 2018.xlsm]DB, AH & KA'!$K$3:$K$200,">="&$B$10,'[Mileage Log 2018.xlsm]DB, AH & KA'!$K$3:$K$200,"<="&$B$11)

 

[BarryL]

try:

=SUMPRODUCT(('[Mileage Log 2018.xlsm]DB, AH & KA'!$O$3:$O$200, ('[Mileage Log 2018.xlsm]DB, AH & KA'!$K$3:$K$200>=$B$10)*('[Mileage Log 2018.xlsm]DB, AH & KA'!$K$3:$K$200<=$B$11))

 

[Aladin Akyurek]

That worked perfect! thank you. I've managed to adjust the similar formulas using your help.

Great to hear.

There is another one i'm struggling with as it has 2 parameters that it searches for. Could you help me with:

=SUMIFS('[Mileage Log 2018.xlsm]DB, AH & KA'!$O$3:$O$200,'[Mileage Log 2018.xlsm]DB, AH & KA'!$K$3:$K$200,">="&$B$10,'[Mileage Log 2018.xlsm]DB, AH & KA'!$K$3:$K$200,"<="&$B$11)

Still using the comma syntax...

=SUMPRODUCT('[Mileage Log 2018.xlsm]DB, AH & KA'!$O$3:$O$200,--('[Mileage Log 2018.xlsm]DB, AH & KA'!$K$3:$K$200>=$B$10),--('[Mileage Log 2018.xlsm]DB, AH & KA'!$K$3:$K$200<=$B$11))