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?

 

[T. Valko]

As I mentioned before the formula worked for a few items, would you be able to tell my why it's not working ofr all.

I'd need to see the file to see what's happening.

If you can post a link to the file I'll take a look.

 

[cwallace70]

I'd need to see the file to see what's happening.

If you can post a link to the file I'll take a look.

https://spreadsheets.google.com/ccc?key=0Alaedde0BPEsdDM5djdOTVBXNlJEOC1SZVgwOEhrTXc&hl=en#gid=0

 

[T. Valko]

I'd need to see the file to see what's happening.

If you can post a link to the file I'll take a look.

https://spreadsheets.google.com/ccc?key=0Alaedde0BPEsdDM5djdOTVBXNlJEOC1SZVgwOEhrTXc&hl=en#gid=0

By posting the file I meant the ACTUAL Excel file.

I can't do anything with a Google spreadsheet plus that link is for a sign-in, it doesn't display a spreadsheet.

 

[cwallace70]

How do I post the actual file...I have never done that before.

Thanks

 

[T. Valko]

How do I post the actual file...I have never done that before.

Thanks

You can't actually post your file here you'd have to put it on some other website and then let us download it from that site. If you don't have access to your own site there are lots of free file hosting sites.

Upload your file to one of those sites then post the link to the file.

 

[cwallace70]

http://www.mediafire.com/?thi9pz59b3t03al

 

[T. Valko]

http://www.mediafire.com/?thi9pz59b3t03al

Ok, I found a couple of problems...

You are trying to use this formula syntax:

=SUMIF(INDIRECT($A$2:$L$2&"!C:C"),$A12,INDIRECT($A$2:$L$2&"!E:E"))

That's one of the reasons it's not working for all the sheets.

Use this formula syntax:

=SUMPRODUCT(SUMIF(INDIRECT($A$2:$L$2&"!C:C"),$A12,INDIRECT($A$2:$L$2&"!E:E")))

Also, some of the sheet names listed in A2:L2 had trailing spaces, like this:

Oct[space]
Sept[space]

Make sure there are no leading/trailing spaces!

 

[cwallace70]

It worked....Thank you for all your help.

 

[T. Valko]

It worked....Thank you for all your help.

You're welcome. Thanks for the feedback!

 

[cwallace70]

For the same spreadsheet you assisted me with, I am trying to create another formula, but I keep getting zero's can you please look at this formula and tell me what I am doing wrong

I want a sum for product code UL for the agency in A7 from all monthly tabs. Jan to Dec are A101 - L101
product code UL is column D. The amounts are in column E, F, & G.
So in essence I want the total thta pertains to agency 46without hardcoding it so it would be $A7 and sum all amounts in columns EFG that have a product code of "UL".

=SUMPRODUCT(SUMIF(INDIRECT($A$101:$L$101&"!C:C"),$A7*(SUMIF(INDIRECT($A$101:$L$101&"!D:D"),UL,INDIRECT($A$101:$L$101&"!E:E")))))


I have tried many different formulas and getting error messages nad zeros.

Thanks in advance for your assistance.