EXCEL FUNCTION SUMIF/SUMIFS

I am trying to bring certain information across from one (EXCEL) workbook to another (EXCEL) workbook.  There could be several records that need to be summed before I bring it across to my summary file.  I have been trying to use the SUMIF and SUMIFS functions in my summary file to bring the data across to my summary file.  I am finding that these functions work very well when you have both workbooks open but lead to a #VALUE! error when the workbook that holds the raw data is not open.  Does someone have a FIX for this ........... PLEASE?
|

David,

No fix for this!

The nature of these function requires the source workbook to be open to keep calculating the result!

You can use SUMPRODUCT function instead of SUMIF/SUMIFS to keep calculating the result when the source workbook is closed as the below syntax:

Instead of SUMIF:

=SUMPRODUCT(--(criteria_range=criteria)sum_range)

Instead of SUMIFS:

=SUMPRODUCT(--(criteria_range1=criteria1)--(criteria_range2=criteria2) ... , sum_range)

You can consider SUMIF/SUMIFS functions are lightweight functions when comparing them to the SUMPRODUCT function.

9 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Haytham:

Thanks so much for replying to my question about ****IF? EXCEL functions and bringing data across from one workbook to another.  My use was to bring checking account information across to a summary actual expenditure spreadsheet, saving time and possible errors by having to input information only once.

Before I got your reply, I had found another solution, replacing SUMIF with SUM(IF....., and I had to school myself in the use of array input methods.  Your suggestion, to use the SUMPRODUCT(--(....... function was far more to my liking, and, undoubtedly, I will have many more uses for the SUMPRODUCT function, now that I have learned about how powerful it is.  Thank you again.

David Hill.

4 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated April 17, 2021 Views 1,866 Applies to: