sumifs returning #value when criteria field is based on vlookup from external source

[Lithium78]

My sumifs function (excel 07) is returning #value. The only reason I can think of is because one of the fields containing sumif criteria has contains a formula with a vlookup to an external data source.

Am I crazy or does SUMIFS not allow external data? Is there a workaround for this?

Basically column B contains a product code that is looked up and column E contains the region the product was sold in. I want to sum column C if B = "AB78" and E= "IN"

If I copy and paste values the formula works fine.. but I really need to figure out a formula that works without this step.

Thank you in advance for any assistance you can provide!

[teylyn]

Hello Lithium

Sumifs works only with open workbooks, so if you nest a Vlookup to an external source, this might be an issue. As a workaround, you could have the Vlookup to the external workbook in a different cell and refer Sumifs to that cell instead.

Or, open the external workbook.

cheers

[daddylonglegs]

....or you could use SUMPRODUCT instead, that will allow you to refer to a closed workbook. What formula are you using?

[Lithium78]

=sumifs(c25:c100,A:A,"AB78",E:E,"IN")

I'm doing this from memory, so if the formula looks wrong hopefully you can get the jist of what I'm doing.

The sum values are in C25:C100... only want to sum if the value is "AB78" in column A and "IN" in column E.

Just so I'm clear on how sumifs works... if I do the product code vlookup in a separate cell (lets say column G) and I change the formula in A to say =G1 the sumifs would work properly?

[daddylonglegs]

That formula returns #VALUE because the ranges don't match, you need it to be like this

=SUMIFS(C25:C100,A25:A100,"AB78",E25:E100,"IN")

If that doesn't fix it and you do have issues referencing closed workbooks then you could try changing to SUMPRODUCT, i.e.

=SUMPRODUCT(C25:C100,(A25:A100="AB78")+0,(E25:E100="IN")+0)

[Lithium78]

That formula returns #VALUE because the ranges don't match, you need it to be like this

=SUMIFS(C25:C100,A25:A100,"AB78",E25:E100,"IN")

If that doesn't fix it and you do have issues referencing closed workbooks then you could try changing to SUMPRODUCT, i.e.

=SUMPRODUCT(C25:C100,(A25:A100="AB78")+0,(E25:E100="IN")+0)

Thanks, I think the sumproduct will work.

The A25:a100 fields contain a Vlookup to our product data sheet... so the results of that lookup are what I am doing my query against.

I appreciate the tip on sumproduct... I wasn't sure how to go about doing this but the +0 makes sense!