Sumif to Sumproduct

[smilking]

Hi there,

Would really appreciate some help with this. . .

I have this sum if formula

=SUMIF('Production Input'!$B$6:$B$309,$D1,('Production Input'!$J$6:$J$309))

And am trying to make it into a sumproduct formual that does the same thing as I have learnt that sumif s do not auto update other sheets if they are not open! whereas sumproducts do.

Would anybody be able to help me convert this as I am not very familiar with the ins and outs of sum Products.

Thanks for your help!!

[smilking]

should probably give a bit more information. . .

Column B on the 'production input' tab is a list of dates

cell d1 is the previous working day =WORKDAY(TODAY(),-1)

So if the date in column B is equal to the date in cell d1, then the information from column J on the 'production input' tab is inputted.

hope that's right (and makes sense. . . and helps)

thank you

[Fotis1991]

Hi smilking.

Try this.

=sumproduct(('Production Input'!$B$6:$B$309,=$D1)*('Production Input'!$J$6:$J$309))

Hope to helps you.

[smilking]

Hi Fortis,

Thank you very much for that! I removed the , before D1 and it works like a charm.

Now hopefully once i have converted all 100 or so SUMIFs to SUMPRODUCTs my other sheet will autopopulate!

Very appreciative of your help

[Fotis1991]

Glad to helps you.

Click the scales to give some Rep if you think an answer deserves it .Click the reputationicon icon . *

*Mark your thread [SOLVED] if you received your answer

[smilking]

Done and Done! Thanks again

[smilking]

one more thing. . . sorry!!

i have completed one workbook and it is working correctly. . . which is great.

However I am trying to edit the second workbook now which has this SUMIF formula

=SUMIF(' Production Input'!$B$67:$B$309,$C3,' Production Input'!$P$67:$P$309)

I have followed the same layout as the sumproduct above, but it is returning a #value error??


Do you have any thoughts on that?

(p.s. there is meant to be a space prior to the 'production input')

[smilking]

As you can see the SUMIF is doing the same as the one before. . . it just doesnt seem to like being a SUMPRODUCT :-(

Thanks

[Fotis1991]

Try this:

=SUMPRODUCT((' Production Input'!$B$67:$B$309=$C3)*(' Production Input'!$P$67:$P$309))

[daddylonglegs]

#VALUE! error might be caused by multiplying by the sum range, e.g. with this formula

=SUMPRODUCT((' Production Input'!$B$67:$B$309=$C3)*(' Production Input'!$P$67:$P$309))

If there is any text in ' Production Input'!$P$67:$P$309, even "null strings" then you get an error. Try a small syntax change

=SUMPRODUCT((' Production Input'!$B$67:$B$309=$C3)*1,' Production Input'!$P$67:$P$309)

[smilking]

Fortis - Thanks once again for getting back to me! I had managed to create that formula and unfortunately the error was still appearing.

DaddyLongLegs - That moderator title is well earned! By trying your modification to the syntax, the formula now works as hoped. . . Could you possibly explain how that small change alters the formula as I am not quite following how it works. Although, I am loving the fact that it does. I have since used the same modifications where I have tried to use a sumproduct in vain before and it has done the trick. very much in your debt

Thank you both very much!