Sumproduct with Vlookup

[cwallace70]

I have a summary sheet with 12 monthly worksheets that I want a sum of the following. It contains 3 criterias.
The summary sheets have a dept code of "EN" and I want a sum from all 12 monthly worksheets that have that same code in column C with an amount in column E.
Ca nthis be done with sumproduct and vlookup or just sumproduct?

 

[cwallace70]

I used the formula belwo and Iam getting a Ref error


=SUM(IF(N:N=Jan!$AJ9,R:R+AE:AE))

 

[Aladin Akyurek]

I used the formula belwo and Iam getting a Ref error


=SUM(IF(N:N=Jan!$AJ9,R:R+AE:AE))

Do we have any #REF! error in any of these references?

We can check this with:

=COUNTIF(N:N,#REF!)
=COUNTIF(R:R,#REF!)
=COUNTIF(AE:AE,#REF!)

What do we get?

 

[cwallace70]

Yes it did have the ref inthe formula. After correcting it
I am now getting a #NUM! error
=SUM(IF(N:N=Jan!$N5,R:R+AE:AE))

 

[T. Valko]

Yes it did have the ref inthe formula. After correcting it
I am now getting a #NUM! error
=SUM(IF(N:N=Jan!$N5,R:R+AE:AE))

If you're NOT using Excel 2007 or later then you can't use entire columns as range references in array formulas.

Try using smaller ranges.

 

[Aladin Akyurek]

Yes it did have the ref inthe formula. After correcting it
I am now getting a #NUM! error
=SUM(IF(N:N=Jan!$N5,R:R+AE:AE))

Hope you don't have any #NUM! error somwhere.
If you are on Excel 2007 or later, this formula would be admissible, provided that
it's confirmed with control+shift+enter, not just enter. On earler versions, we are not allowed
to reference whole columns.

 

[cwallace70]

If you're NOT using Excel 2007 or later then you can't use entire columns as range references in array formulas.

Try using smaller ranges.

Thanks It worked like a charm....

 

[T. Valko]

Originally Posted by T. Valko
If you're NOT using Excel 2007 or later then you can't use entire columns as range references in array formulas.

Try using smaller ranges.

Thanks It worked like a charm....

You're welcome. Thanks for the feedback!