Solved Summarizing values with same number in different column

May 14, 2013 at 13:54:29
Specs: Windows 7
I would like to have a formula that would look at a column of numbers and sum up all the values that are to the right of those same numbers. Ex) Column F has numbers 8-25, column J-M has dollar values, so I would all the number "8" values summarized on one line.

See More: Summarizing values with same number in different column

Report •

May 14, 2013 at 16:39:19
✔ Best Answer
For this solution to work, all ranges must be equal.
For example, you Numbers column F uses only 18 rows,
8 thru 25, but your Dollar Values columns J - M
may use more then 18 rows.
But the formula requires that all ranges be the same size.


As you can see, I have used 30 rows for all the columns even
though column F needs only 18.

If you need more than 30 rows, change to suit your needs,
but make sure you change both parts.

Also, to make it more versatile, you can replace the
hard coded number 8 with a cell reference.

See how it works for you.


Report •

May 15, 2013 at 05:46:12
It is giving me a #VALUE error. This is my exact formula I put in : =SUMPRODUCT((F3:F152=13)*J3:M152)

Am I doing something wrong?

Report •

May 15, 2013 at 06:50:19
Your formula works fine for me.

With this data, your formula returned 5 (3 from Row 4 plus 2 from Row 6)

    F ...   J      K        L      M
3   1       1               2	
4  13              1        2
5   3              1               2	
6  13       2		

However, if one of the cells in J:M is not a number, then you will get a #VALUE error.

For example, enter Tom in J4 and the formula will return #VALUE. Even a space will cause the error, so make sure that any empty cells are really empty.

If you are still having problems, the please click on the following line, read the instructions on how to post example data in this forum and then post a short example of your data like I did above.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

May 15, 2013 at 07:05:14
That was it, I had a value that had a space in it. Thanks it works great now!!

Report •

Ask Question