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.

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.=SUMPRODUCT((F1:F30=8)*J1:M30)

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.

MIKE

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?

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 2However, 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.

That was it, I had a value that had a space in it. Thanks it works great now!!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History