Obtaining all values in a specific column with a fixed lookup (text) value [Solved]

[DlarsD]

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!

[#2]

@DlarsD 

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.

[#3]

@DlarsD Just beaten by @mathetes . I thought of SUMIF as well. Perhaps the attached file achieves what you need.

[#4]

@Riny_van_Eekelen 

It's a red-letter day on my calendar when I can beat you to the punch, Riny.

[#5]

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!