Sumproduct question

[cwallace70]

I would like to perform a sumproduct for the following but I keep getting value as the sum. The formula is as follows
=SUMPRODUCT(--(A2:A50000="LU"),--(LEFT(E2:E50000,1)="K"),--(H2:H50000))

My spreadsheet have a dept in column A, a code in column E and an amount in column H.
I want to get a sum if column A have the dept code of "LU" and a code of "H" then sum all the amounts in column H that fits those first two criteria's. I am using excel 2003 and cannot get this formula to work. Please help. What am I doing wrong

 

[cwallace70]

The formula works. thanks, I have another question about the same information from my previous thread, How can Iget all this information onto a summary sheet, by eitther doing a VLOOKUP, or a SUMPRODUCT that will pull the criterias I have fro mthe previous thread as well as from 12 months without doing a pivot table.

 

[Fazza]

For getting all the information onto a summary sheet - from multiple source sheets if I understand correctly - without a pivot table (and also by the way without VLOOKUP, SUMPRODUCT or ANY other formulas) and without VBA code and without needing to use advanced filter or pre-set lists of codes to report (so the lists autogenerate each time, either from a fixed list or purely reporting the used codes or whatever else you want to define), please use a query table.

If you wanted to include some VBA it could be made smarter. As it works in a database fashion (like a pivot table) it should be efficient - in particular, much faster than large numbers of SUMPRODUCT formulas pulling data from worksheets with large amounts of data.

No formulas, no code, no pivot table. All the lookups done via table joins in SQL. It is really good. Suggest you google for examples of Excel external data queries, or just dive in via menu ALT-D-D-N.

cheers

 

[cwallace70]

I am not familiar with VBA's but the formula I am using for the summary sheet is
=SUMPRODUCT(--(Jan:Dec!$A$3:$A$50000=$A7),--(Jan:Dec$E$3:$E$50000=$C6),Jan:Dec!$H$3:$H$50000)
and I keep getting a $NAME? error. Can you help me with this formula.
Although it make take a little time to update as each months data is entered in the monthly tab, I still prefer to use the formula method.

 

[Aladin Akyurek]

I am not familiar with VBA's but the formula I am using for the summary sheet is
=SUMPRODUCT(--(Jan:Dec!$A$3:$A$50000=$A7),--(Jan:Dec$E$3:$E$50000=$C6),Jan:Dec!$H$3:$H$50000)
and I keep getting a $NAME? error. Can you help me with this formula.
Although it make take a little time to update as each months data is entered in the monthly tab, I still prefer to use the formula method.

If you are on Excel 207 or later, you could create a range housing the names of the relevant sheets Jan, Feb, ..., Dec, and invoke:

=SUMPRODUCT(
    SUMIFS(
      INDIRECT("'"&SheetList&"'!$H$3:$H$50000"),
      INDIRECT("'"&SheetList&"'!$A$3:$A$50000"),$A7,
      INDIRECT("'"&SheetList&"'!$E$3:$E$50000"),$C6))

If on an earlier version, you could try the SumIf version of the foregoing
formula if you can concatenate together the relevant condition ranges into a new range. Note that this boils down to having 50000 formulas however simple.