Sumproduct question

cwallace70

Board Regular
Joined
Mar 7, 2011
Messages
172
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
 
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.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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.
 
Upvote 0
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:

Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top