SUMIF vs SUMPRODUCT

[EAGLEBUCKS]

Hi I am new here. My name is John and I am starting to have to use excel 2007 more at work. I am currently trying to add values from two different workbooks into a third excel sheet. I tried using this equation: =SUMIF('[Wk07_CA5127_Cost Planning.xlsx]December 2010'!$D$7:$D$1000,$A24,'[Wk07_CA5127_Cost Planning.xlsx]December 2010'!G$108:G$278)+SUMIF('[Wk07_CA5258_Cost Planning.xlsx]December 2010'!$D$7:$D$1000,$A24,'[Wk07_CA5258_Cost Planning.xlsx]December 2010'!G$108:G$278).

This equation worked well but I noticed as soon as I closed the workbooks it turned from a numerical value to #VALUE. I have been doing some research on the SUMPRODUCT function but am confused how I would translate the above formula into the SUMPRODUCT formula. Can anyone help me out?

Thanks
Johnny

[daddylonglegs]

Hello John, Please don't post the same question in multiple forums - I'm deleting your other questions.....

[daddylonglegs]

OK, I didn't delete them, shg locked your other posts.....

A SUMIF of this form

=SUMIF(A1:A10,C1,B1:B10)

can be written as a SUMPRODUCT of this form

=SUMPRODUCT((A1:A10=C1)+0,B1:B10)

[EAGLEBUCKS]

Sorry about that I was unsure where to post it. I will not do that again my bad

[EAGLEBUCKS]

Okay I tried that and still got #VALUE. Does something look wrong with this equation?

=SUMPRODUCT(('[Wk07_CA5127_Cost Planning.xlsx]December 2010'!$D$7:$D$56=A37)+0,'[Wk07_CA5127_Cost Planning.xlsx]December 2010'!$G$108:$G$278)+SUMPRODUCT(('[Wk07_CA5258_Cost Planning.xlsx]December 2010'!$D$7:$D$56=A37)+0,'[Wk07_CA5258_Cost Planning.xlsx]December 2010'!$G$108:$G$278)

[daddylonglegs]

You have mismatched ranges, the first range is 50 rows ($D$7:$D$56) the second is 171 ($G$108:$G$278), they need to be the same size, normally I'd expect the ranges to start on the same row too, don't you want to sum numbers on a row if the criterion is met on the same row?

Your SUMIF has the same problem but SUMIF won't give you an error, it will just adjust the sum range to be the same size as the criteria range (which might not actually give you the result you expect)

[shg]

The two ranges have to be the same size. The first is 50 rows and the second is 171.

Please Login or Register  to view this content.

Also, you are (or would be) computing the same value twice.

[EAGLEBUCKS]

Thanks for your responses. No they are the same cells but two different work books so they wouldn't be the same exact thing twice. I am going to try your formula now and hopefully this will work haha

[EAGLEBUCKS]

It worked great thanks for all the help