Forum Discussion
Obtaining all values in a specific column with a fixed lookup (text) value [Solved]
Hi everyone,
I'm trying to figure out a smarter and more convenient way to sum multiple cells with a fixed lookup value in an extensive sheet. Using a vlookup, or an index-match gives the first value it finds, however, not all values with respect to the specific lookup value. A nested vlookup or index-match would be very cumbersome due to the extensive list, so this is not what I'm looking for; also manually summing the respective rows is definitely not worth doing.
Attached a simplified example which is hopefully clear.
- The goal is to sum all revenues from, for instance, January, for product 1. As mentioned before, a vlookup or index-match would return the value from client 1, however, not from all clients. An indefinite table array does not do the trick obviously.
Any thoughts on this?
Thanks in advance!
4 Replies
in the absence of an actual spreadsheet from you (an image is of some, but limited, use) I can point in the direction of a couple of possible solutions.
- Have you tried the SUMIF function? That might do it: it will sum a given column based on a criterion or criteria you specfiy.
- And if you have the newest version of Excel, there are some Dynamic Array functions that might work in conjunction with other summing functions.
- Of course, there's also the Pivot Table which would do every product by month it's perfect for cross-tabulated summary reports.
Might I suggest you upload an actual representative file, if not your actual file (which may contain proprietary info) if you'd like more specific help.
- I initially also tried the SUMIF, but I must have made a mistake because it works now.
Thanks for the reply on this silly question!
