Do we have any functions to replace sumifs

[kusidin]

Hi Excel Guru,
I have an issue when linking sumifs to the data source. Sometimes the sumifs data will become error if the source data is not open.
Can I have a excel function to replace sumifs. In this case the calculation wont show as error even the source data is not open.

Thanks in advance
Krusidin

[Special-K]

SUMPRODUCT
or SUM(IF(...)) Array formula

[kusidin]

Hi Special-K,
I am ok with the sum(if) array formula now but can you please explain how to use sum product to resolve the issue.
Attached is the data for your reference.
Thanks
Krusindin

[Special-K]

I didnt actually work out a solution, I saw someone provide SUMPRODUCT as an answer though nothing specific.

=SUMPRODUCT((A1:A12="A")*(B1:B12=G1)*(C1:C12))

So its saying if A1:A12 equals "A" and B1:B12=G1 then where that is true retrieve the values from C1:C12 and sum them.

This produces 208 but you're talking about a closed workbook so you'll need to amend the formula to see if this still works.

[kusidin]

Thanks special-k!