Sumproduct returning #N/A on multiple criteria

Hi

Read a previous post with an identical issue as me but still cannot resolve my problem! I am trying to create a sumproduct on multiple criteria - I cannnot see anything wrong with the formula but it still does not work! As per the OP on the post I looked at, I am now tearing my hair out! I have inserted curly brackets via ctl shft and enter but with or without them it still does not work. The $A$8, and C$3, I have copied the values from the data sheet so there should be no issue with #n/a due to formatting. Please help!

=SUMPRODUCT(('[2016 GL transactions.xlsx]Sheet1'!$P$2:$P$64188=$A$8)*('[2016 GL transactions.xlsx]Sheet1'!$K$2:$K$64188=C$3)*('[2016 GL transactions.xlsx]Sheet1'!$D$2:$D$64188))

Are you trying to sum on Sheet1 D2:D64188? If so,

=SUMPRODUCT(('[2016 GL transactions.xlsx]Sheet1'!$P$2:$P$64188=$A$8)*('[2016 GL transactions.xlsx]Sheet1'!$K$2:$K$64188=C$3),'[2016 GL transactions.xlsx]Sheet1'!$D$2:$D$64188)

If not, then maybe post a sample so I get a better idea.

I think the most likely cause of the #N/A with that formula is if there are any existing #N/A errors within any of the referenced ranges.

If that's the case, try SUMIFS instead
=SUMIFS('[2016 GL transactions.xlsx]Sheet1'!$D$2:$D$64188,'[2016 GL transactions.xlsx]Sheet1'!$P$2:$P$64188,$A$8,'[2016 GL transactions.xlsx]Sheet1'!$K$2:$K$64188,C$3)

Hi all

Thanks but I use SUMIFS all the time and I need to have the data without the source file being open so SUMIFS is not an option.

How do i post an example as per swiftrain please?

instead of "post quick reply", click on advanced and scroll down to "Manage attachments", you can upload from there. It will default to inline, which is fine.

OK thanks. I think I have resolved my problem...by accident I shortened the range and it worked and I realised that the fault was occurring on a cell with #N/A data in my range, therefore it kind of stopped reading the whole data list.

Seems to be working for now but thanks anyway, I am sure I'll be back with more issues at some point.

You're welcome.