EXCEL FUNCTION SUMIF/SUMIFS

Question

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?

Answer 1

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.

Answer 2

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.