R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Hello,

Can you help me with an Excel (2007) formula to determine the sum of several values in a particular range when the basis for which values to include is defined by values in a different range?

For example, suppose each cell in the range B3:B11 contains some random value as shown in the first screenshot below. Furthermore, suppose some of the cells in the adjacent range C3:C11 contain an "x":

1) What formula would be best to use in cell C2 to display the sum of the column B values for which, in column C, an "x" is present in the corresponding row (i.e. 3 + 4+ 1 +1 = 9)?

2) and, so that the formula could be pasted at the top of additional columns to display a similar total for each:

3) and finally, so that any other letter or symbols could be used in place of "x" and still give the totals:

I'm pretty sure the answers to 2 & 3 are to: make the column B reference absolute and use ...,<>"",... respectively but I just wanted to include them to be sure.

Thanks EE-20101223.xlsx

Can you help me with an Excel (2007) formula to determine the sum of several values in a particular range when the basis for which values to include is defined by values in a different range?

For example, suppose each cell in the range B3:B11 contains some random value as shown in the first screenshot below. Furthermore, suppose some of the cells in the adjacent range C3:C11 contain an "x":

1) What formula would be best to use in cell C2 to display the sum of the column B values for which, in column C, an "x" is present in the corresponding row (i.e. 3 + 4+ 1 +1 = 9)?

2) and, so that the formula could be pasted at the top of additional columns to display a similar total for each:

3) and finally, so that any other letter or symbols could be used in place of "x" and still give the totals:

I'm pretty sure the answers to 2 & 3 are to: make the column B reference absolute and use ...,<>"",... respectively but I just wanted to include them to be sure.

Thanks EE-20101223.xlsx

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paste the formula in C2 and drag it into the rest of the row headers

=SUMPRODUCT(($B3:$B11)*(C3

EE-20101223-1-.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialThe optimal function for a single condition is SUMIF, non-array and more efficeint than SUMPRODUCT. For "x"s this formula in C2 copied across

=SUMIF(C3:C11,"x",$B3:$B11

for just "not empty" you can use the criteria "<>", i.e.

=SUMIF(C3:C11,"<>",$B3:$B1

regards, barry

Merry Christmas!

You could use SUMPRODUCT for the purpose.

=SUMPRODUCT(($B3:$B11)*(C3

Can you explain the multiplication indicator (*) in this formula? I looked up =SUMPRODUCT() which has this syntax:

SUMPRODUCT(array1,array2,a

and from the documentation it seems that the multiplication is supposed to be done automatically with the resulting products then being summed. So why is the * needed in this case?

A simple SUMIF will do it:

=SUMIF(C3:C11,"<>",$B$3:$B

The SUMIF formula certainly is the shortest and it works. However, although I understand that <> is "not equal to," I have no clue how the "<>" works as the 2nd argument in this formula. Please explain.

Nevermind. Barry gave the explanation:

for just "not empty" you can use the criteria "<>", i.e.

=SUMIF(C3:C11,"<>",$B3:$B1

Thanks for explaining it Barry. One question it raises though. For "not empty," I always use <>"" as in "not equal to nothing." However, does the simple <> work just as well. In other words, does <> actually mean "not empty" all the time or just in this case?

Rather than

=SUMPRODUCT(($B3:$B11)*(C3

some people prefer to use a syntax like this

=SUMPRODUCT(--($B3:$B11),-

The results will be the same but I believe the second one is very marginally more efficient. In the first one the arrays are multiplied to form

With the second syntax there are two arrays of TRUE/FALSE values, explicitly converted by using -- to 1/0 values. SUMPRODUCT then works on the 2 arrays, so the result is the same but the methods are different.

You can use other operators for the explicit conversion from TRUE/FALSE to 1/0. Typically any mathematical operation that doesn't change the value like adding 0 or multiplying by zero. I favour +0, partly because there have been instances in Excel 2007 where using -- in SUMPRODUCT leads to it not updating when values were changed, i.e.

=SUMPRODUCT(($B3:$B11)+0,(

Note that given the fact that in the first formula above the single array only needs to be summed, that version can use SUM rather than SUMPRODUCT, i.e.

=SUM(($B3:$B11)*(C3:C11<>"

The downside is that you need to "array enter" that version which you don't with the SUMPRODUCT

If you haven't see Bob Phillips write up on SUMPRODUCT its worth reading here

regards, barry

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

for example:

in cell C2 add the following formula and press Ctrl+Shift+Enter:

=SUM(IF((C3:C11<>""),$B$3:

You can drag to cover the header of the other columns.

EE-20101223-updated.xls